VBA to filter...

[lucky12341]

I need to autofilter column B in place taking out the Blank Cells. I dont want to delete and I want to steer away from using the Autofilter option. Is there a way to do this via VBA so I can turn can filter with one button then remove the filter with another? Thanks

 

[Andrew Poulsom]

Record a macro while AutoFiltering column B for Blanks and assign it to a Button. Record another macro removing AutoFilter and assign it to another Button.

 

[Joe Was]

This will toggle the row hide on or off each time it is run.

Edit: If you used the original un-tested posted code and it did not toggle, I replaced that code with this corrected code that is tested and works:

Sub myHideBlnkR()
'Hide all rows with blank in any row of column "B."
'Run from sheet module, like: Sheet1.
Dim myLastR&, rOffser&, cnt&

myLastR = Range("B65536").End(xlUp).Offset(1, 0).Row

Range("B1").Select
rOffset = 0

'Do until last row of data!
While Range("B1").Offset(rOffset, 0).Row <> myLastR

If Range("B1").Offset(rOffset, 0).EntireRow.Hidden = True Then cnt = cnt + 1

'Test for blank value.
If Range("B1").Offset(rOffset, 0).Value = "" Then
'Hide 0 value row.
Range("B1").Offset(rOffset, 0).EntireRow.Hidden = True
End If

'Goto next line
rOffset = rOffset + 1
Wend

If cnt > 0 Then Columns("B:B").EntireRow.Hidden = False
End Sub

 

[lucky12341]

Joe Was,
SWEET!!! that is exactly what I wanted...thanks so much man

 

[lucky12341]

Last question, is there a way to speed this command up? It works wonderful but even when I make the screen updating false it takes quite awhile...

 

[Joe Was]

Yes, remove the "If" tests and break the code up into two Sub's one to Hide and one to Show!


Sub myShow()

Columns("B:B").EntireRow.Hidden = False
End Sub


Sub myHide()

Dim myLastR&, rOffser&

myLastR = Range("B65536").End(xlUp).Offset(1, 0).Row
Range("B1").Select
rOffset = 0

While Range("B1").Offset(rOffset, 0).Row <> myLastR

If Range("B1").Offset(rOffset, 0).Value = "" Then _
Range("B1").Offset(rOffset, 0).EntireRow.Hidden = True

rOffset = rOffset + 1
Wend
End Sub

 

[lucky12341]

Joe Was,
Your code works great, splitting it up makes it go mildly faster. I ran the code both ways and I found that it takes like 10 minutes to go through the worksheet. There is 10,425 rows total, I can use the autofilter one and that takes maybe 5 seconds to process. Is it possible to make it run that fast or make it take near 30 seconds? I can forward an example of the workbook if that helps, I appreciate all the help.

 

[Joe Was]

See if this is any better, I tested it on 12,000 rows and it was instant [This style of looping is the fastest loop code, the only faster code is directly setting cell properties, which I think will not work here?]?

Sub hideRBCells()
Dim myLastR&, BRng As Variant

myLastR = Range("B65536").End(xlUp).Offset(1, 0).Row

Set BRng = Range(Cells(1, 2), Cells(myLastR, 2))

Application.ScreenUpdating = False

For Each Cell In BRng
If Cell.Value = "" Then Cell.EntireRow.Hidden = True
Next Cell

Application.ScreenUpdating = True
End Sub



Sub myShowRB()
Columns("B:B").EntireRow.Hidden = False
End Sub

 

[Von Pookie]

I just had an idea...

If the cells are *truly* blank (not just appearing blank due to a formula result, for example), this should work:

Sub test()
Dim myRng As Range

Set myRng = Range("B3", Range("B65536").End(xlUp))

myRng.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = Not myRng.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden

End Sub

Assigning that to a button should toggle the hidden true/false value of the rows with blank cells in column B.

Again, it all depends on the cells. This may not work out for you.

 

[Joe Was]

Yes, both are faster than the original code, tested both on the same data, and "SpecialCells" is slower by about "1/3 rd to half" again as much, which is still nearly instant.

It was slick of you to think of setting the "Not" relation though, I thought of testing with "SpecialCells" but never thought to set it to the "Not" condition!

In the post above: "the only faster code is directly setting cell properties, which I think will not work here?"

Was to use SpecialCells to set cell properties like BOLD and such and I never thought to use: Itself to test for Blank and Not Hidden, hence the "?" I was hoping someone would fix that, thanks it makes my day when I learn something!