Macro hide rows with blank data in cells
 

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.

Macro hide rows with blank data in cells
 

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
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




" wrote:

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.

Macro hide rows with blank data in cells
 

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

Macro hide rows with blank data in cells
 

Dim BotRow as Long
BotRow=Range("E65536").End(xlUp).Row
For X = 10 To BotRow

HTH
wrote:
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

Macro hide rows with blank data in cells
 

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)
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




" wrote:

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

Macro hide rows with blank data in cells
 

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

Macro hide rows with blank data in cells
 

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?