Autofilter past blank rows

[lunker55]

I have a macro that puts two blank rows between every row that has a
different day. The problem is the autofilter does not see anything past the
blank rows.
It worked on my old file, and I can't remember how I did it. I'm sure
someone on this group showed me how to do it the first time.
Thanks,
Joe

 

[Myrna Larson]

You must select the entire list before you go to Data/Filter/AutoFilter.

BTW, to eliminate problems like this, I would delete the blank rows and have
your macro increase the row height when the date changes. The visual effect is
the same, problems like the current one are eliminated.

 

[lunker55]

Thank you thank you thank you. So simple!
I also like your idea of changing the row height at every date change.
But I'm not sure I like having some rows with data having different row
heights.
Joe

 

[Dave Peterson]

This uses dates in column A:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Set wks = Worksheets("sheet1")
With wks
.UsedRange.Rows.AutoFit
FirstRow = 2 'headers in 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
'do nothing
Else
.Rows(iRow).RowHeight = .Rows(iRow).RowHeight * 2
End If
Next iRow
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[lunker55]

Thanks Dave. I'll give it a try.
Joe

This uses dates in column A:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Set wks = Worksheets("sheet1")
With wks
.UsedRange.Rows.AutoFit
FirstRow = 2 'headers in 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
'do nothing
Else
.Rows(iRow).RowHeight = .Rows(iRow).RowHeight * 2
End If
Next iRow
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm