Select cells with certain format

[wvandenberg]

I have some code that selects the used range on my spreadsheet.

I need help doing the following: I would like to select all the cells within this range that have a number format of "General". Then I want to delete the entire row containing each of these cells.

My main problem is that when I delete a row and the rows shift up, the loop advances to the next row and basically skips every second row. I also wrote another loop that puts the rows into an array but I still can't select all the rows to be deleted and delete them all at once. Here is the second loop if anyone needs it to help me.

Function HeaderRows() As Variant
Dim sRows() As String 'Declares a dynamic array variable
Dim sTmp As String
Dim i As Integer 'Counter
Dim c As Variant 'Rows that contain header info in the middle of the file

ActiveSheet.UsedRange.Select
Range(ActiveCell.Address, Cells(ActiveSheet.UsedRange.Rows.Count, ActiveCell.Column)).Select

i = 0
For Each c In Selection
If c.NumberFormat = "General" Then
sTmp = c.Address
End If

While sTmp <> Empty
ReDim Preserve sRows(0 To i)
sRows(i) = sTmp
i = i + 1
sTmp = &quot;&quot;
Wend
Next

MsgBox i & &quot; header rows are found in the sheet&quot;

Erase sRows ' deletes the varible contents, free some memory
End Function

I hope this post isn't too confusing. Let me know if I can provide any additional info.

Thanks,
Wendy

 

[Zathras]

Here is one way:
[blue]

Option Explicit

Sub DeleteGeneralFormattedRows()
Dim r As Range
Dim c As Range
Dim nFirstRow As Long
Dim nLastRow As Long
Dim i As Long
Dim bDeleteRow As Boolean

  With ActiveSheet
    nFirstRow = .UsedRange.Cells(1, 1).Row
    nLastRow = .UsedRange.Rows.Count + nFirstRow - 1
    For i = nLastRow To nFirstRow Step -1
      Set r = Intersect(.UsedRange, Range("A" & i & ":IV" & i))
      bDeleteRow = False
      For Each c In r
        If c.NumberFormat = "General" Then
          bDeleteRow = True
          Exit For
        End If
      Next c
      If bDeleteRow Then
        r.EntireRow.Delete
      End If
    Next i
    Set r = Nothing
  End With
End Sub

[/color]

 

[wvandenberg]

Thank you very much, I'll give it a try.

Wendy