CODEWITHSUNDEEP
vbaexcel
Charles W
Charles W

Reputation: 2292

How do i clear all formatting outside of UsedRange in VBA for Excel?

I have used Cells(1,1).EntireRow.InteriorColor.Color to highlight an entire row, and now i need to clear the formatting of everything outside of the range of UsedRange. Is there a way to do this?

Whenever I google, "outside of range," I only get links to array out of bounds errors. Any help is appreciated.

Thanks!

Upvotes: 1

Views: 2965

Answers (3)

ArrGee
ArrGee

Reputation: 1

A simpler method is to use named ranges that are outside the selection.

So assuming a range Cell(1,1) to Cell(rowActiveNum, colActiveNum) the following will clear all formatting outside the data required. 

    rowNum = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    colNum = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column

    'Remove any excess columns rows that are no longer used

    referStr = "=OFFSET('Source'!$A$1," & rowNum + 1 & ", 0, 1000000 - rowNum, 16300)"
    ActiveWorkbook.Names.Add Name:="SOURCE_EMPTY_ROW", RefersTo:=referStr
    ActiveSheet.Range("SOURCE_EMPTY_ROW").Delete

    referStr = "=OFFSET('Source'!$A$1, 0, " & colNum + 1 & ", 1000000, 16300 - colNum)"
    ActiveWorkbook.Names.Add Name:="SOURCE_EMPTY_COL", RefersTo:=referStr
    ActiveSheet.Range("SOURCE_EMPTY_COL").Delete

Works for Excel 2007. with the total number of available columns approx 16300 (2^14) and The total number of available rows approx 1000000 (2^20)

Upvotes: 0

Tony Dallimore
Tony Dallimore

Reputation: 12413

I tried posting this last night but could not get past "The server is to busy to display the web page" and "An error has occurred posting your answer". This answer is different from SimpLE Man's so I will try posting today.

Option Explicit
Sub ClearFormattedCellsOutsideRangeWithValues()

  Dim ColLastUsed As Long
  Dim ColLastWithValue As Long
  Dim RowLastUsed As Long
  Dim RowLastWithValue As Long
  Dim RngUsed As Range


  With Sheets("Sheet1")
    RowLastWithValue = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
    ColLastWithValue = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column

    ' Calculate last row and column allowing for top rows and left columns being unused
    ColLastUsed = .UsedRange.Columns.Count + .UsedRange.Column - 1
    RowLastUsed = .UsedRange.Rows.Count + .UsedRange.Row - 1

    Debug.Print "ColLastWithValue " & ColLastWithValue
    Debug.Print "ColLastUsed " & ColLastUsed
    Debug.Print "RowLastWithValue " & RowLastWithValue
    Debug.Print "RowLastUsed " & RowLastUsed

    If ColLastUsed > ColLastWithValue Then
      .Columns(ColNumToCode(ColLastWithValue + 1) & ":" & _
               ColNumToCode(ColLastUsed)).EntireColumn.Delete
    End If

    If RowLastUsed > RowLastWithValue Then
      .Rows(RowLastWithValue + 1 & ":" & RowLastUsed).EntireRow.Delete
    End If

  End With
End Sub
Function ColNumToCode(ByVal ColNum As Long) As String

  Dim Code As String
  Dim PartNum As Long

  ' Last updated 3 Feb 12.  Adapted to handle three character codes.
  If ColNum = 0 Then
    ColNumToCode = "0"
  Else
    Code = ""
    Do While ColNum > 0
      PartNum = (ColNum - 1) Mod 26
      Code = Chr(65 + PartNum) & Code
      ColNum = (ColNum - PartNum - 1) \ 26
    Loop
  End If

  ColNumToCode = Code

End Function

Upvotes: 0

simpLE MAn
simpLE MAn

Reputation: 1622

This clears the formatting outside the used range of the actual WorkSheet:

Sub clearFormatOutsideUsedRange()

    Dim calcState As XlCalculation
    calcState = Application.Calculation
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim UpperRng As Range
    Dim LowerRng As Range
    Dim j As Long
    Dim startRow As Integer
    For j = 1 To Me.Columns.Count
        If Me.Cells(1, j).Value = "" Then
            Set UpperRng = Me.Range(Me.Cells(1, j), _
                              Me.Cells(1, j).End(xlDown).Offset(-1, 0))
            UpperRng.ClearFormats
        End If

        If Me.Cells(Me.Rows.Count, j).Value = "" Then
            Set LowerRng = Me.Range(Me.Cells(Me.Rows.Count, j).End(xlUp).Offset(1, 0), _
                              Me.Cells(Me.Rows.Count, j))
            LowerRng.ClearFormats
        End If
    Next j

    Application.ScreenUpdating = True
    Application.Calculation = calcState

End Sub

This is a shot before running the marco:
enter image description here

And this one is the result:
enter image description here

Upvotes: 3

Related Questions