Macro hide rows with blank data in cells

T

thisisbowling

Hello. Can anyone help?

I need a simple macro that looks down column E to find the last row
with data in it (say row 118) and then goes from 10 to 118 looking in
column G hiding the row if there's no data in the cell. I've tried
but with no luck, can anyone help? There must be many ways to do this
but KISS is the best...

Matt.
 
M

Michael

Sub Hiderows()
Dim iLastrow As Integer
'This is your last row in col a
iLastrow = Range("A65536").End(xlUp).Row
For i = 1 To iLastrow
If Cells(i, 1) = "" Then
Cells(i, 1).EntireRow.Hidden = True
End If
Next i


End Sub
 
T

thisisbowling

I just need a way now to figure out the last cell in Column E that has
data so I don't go all the way to the bottom to replace the "118" to
allow for varied length charts.

Sub HideBlankSales()
Dim X As Integer
For X = 10 To 118

If Cells(X, 7).Value = 0 Then
Rows(X).Hidden = True
Else
Rows(X).Hidden = False
End If
Next X
End Sub
 
M

Michael

Change the A to in on the following statement:
iLastrow = Range("A65536").End(xlUp).Row
so it looks like this:
iLastrow = Range("E65536").End(xlUp).Row

Then change the 1 in Cells(i,1) to Cells(i,5)
 
T

thisisbowling

Thanks, the final code is below but if there is a better way or faster
to achieve the same result let me know. the data could go all the way
to ~5000 rows...

Sub HideBlankSales()

Dim X As Integer
Dim BotRow As Long
BotRow = Range("E65536").End(xlUp).Row

For X = 10 To BotRow
If Cells(X, 7).Value = 0 Then
Rows(X).Hidden = True
Else
Rows(X).Hidden = False
End If

Next X

End Sub
 
T

thisisbowling

I tried using "Application.ScreenUpdating = False" to speed things up
but it still took 25 secs. Then I found this code and it only took 3
secs, this is an example that was previously posted...

Sub HideBlankRowsInCol()
Dim myRg As Range

Set myRg = Range([e7], [e65536].End(xlUp))

On Error Resume Next
Set myRg = myRg.Offset(0, 2).SpecialCells(xlCellTypeBlanks)
If Err = 0 Then
myRg.EntireRow.Hidden = True
Else
MsgBox "No blanks"
End If
Set myRg = Nothing
End Sub

Which I could just simplify to

Sub HideBlankRowsInCol()
Dim myRg As Range
Set myRg = Range([e7], [e65536].End(xlUp))
Set myRg = myRg.Offset(0, 2).SpecialCells(xlCellTypeBlanks)
myRg.EntireRow.Hidden = True
End Sub

This code works great and I've learnt a little... Thanks for the help
HTH. Did you know of this method?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top