Module for Hiding Blank Rows

[PokerZan]

Hi guys,

I'm just starting with VBA and I'm running into a situation that I'm not understanding...

I am trying to write a module for a sheet that will hide rows (between 1 and 42) when cell Ax is blank. Here is the code that I have so far but it is only hiding one row (the first row where Ax is blank).

************************************************

Sub HideBlankRows()

' select all the rows
Application.ScreenUpdating = False
Dim myLastRow As Long
Range("A1").Select
On Error Resume Next
myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myRange = "a1:" & "a42"
Application.ScreenUpdating = True
Range(myRange).Select

' hide empty rows
Rng = Selection.Rows.Count
ActiveCell.RowOffset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value = "" Then
Selection.EntireRow.Hidden = True
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
Application.ScreenUpdating = True

End Sub

**************************************************
Any ideas?

 

[Tom Urtis]

If "blank" means empty (as opposed to cells containing null string formulas or spacebar characters to make them look blank) then

Range("A1:A42").SpecialCells(4).EntireRow.Hidden = True

In case there are no blanks:

On Error Resume Next
Range("A1:A42").SpecialCells(4).EntireRow.Hidden = True
Err.Clear

If there are such blank non-empty cells:

Dim i%
For i = 1 To 42
If Len(Cells(i, 1).Value) = 0 Then Rows(i).Hidden = True
Next

 

[venkat1926]

in a macro deleting rows in youur for.....next loop start from the last row to the first row.

sample code statments would be like this

Dim j As Integer, k As Integer
j = Cells(Rows.Count, "a").End(xlUp).Row
For k = j To 1 Step -1
If Cells(k, 1) = "" Then Cells(k, 1).EntireRow.Delete
Next

 

[Tom Urtis]

I believe he wanted to hide rows, not delete them.

 

[PokerZan]

Hi Tom,

thank you, here is the forumla in all the cells in Column A that I'm trying to determine if they are "blank":

=IF(Input!$E12<1,"",Input!A12)

I have always referred to that as "", as blank, is that incorrect?

 

[Tom Urtis]

See my edited suggestion for that in my first post, I think we posted at around the same time.

Actually, "blank" where autofilter is concerned does mean both empty and null string, the nomenclature can get confusing at times, so you could use autofilter but with only 42 rows a loop is not a heavy burden and should be OK.

 

[PokerZan]

Yes, this is a workbook that I use to update real time stats/metrics throughout the day and as agents come online and go offline I want to hide the rows to make it clean.

 

[PokerZan]

sorry for the confusion, just where would I place that within the code (and what would it replace)?

Also, for my edification, can you tell me why it is only hiding one row?

 

[Tom Urtis]

This is a macro that goes into a standard module just like the macro you have, shortened to this:

Sub HideBlankRowsX()
Dim i%
For i = 1 To 42
If Len(Cells(i, 1).Value) = 0 Then Rows(i).Hidden = True
Next
End Sub

It will hide whatever row(s) has/have no character of any kind in its column A cell.

Take a close look at the column A cell of a row you think should have been hidden, maybe it is row 8 so cell A8 is what you are looking at, and in another cell enter the formula
=LEN(A8)
and the answer will be a number greater than zero.

That means cell A8 contains as many characters (even space bar characters) as the number returned by that formula.

You can see what kind of character that is by entering
=CODE(LEFT(A8))
and the leftmost (first) character's ascii code number will be returned.

 

[PokerZan]

Thanks a million Tom! This worked like a champ... I know there is always a better way to skin a cat, but can you tell me where my code was wrong? I know it worked on a single row but I was and am very preplexed with why...

Thanks again!