Autofilter past blank rows

L

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
 
M

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.
 
L

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
 
D

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
 
L

lunker55

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

Dave Peterson said:
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
Click to expand...
 
Top