Suppressing rows where a cell is blank

[craigh]

I am trying to suppress rows where a certain cell in that row is blank.
What is the best way to do that?

Thanks,
Craig

 

[Ron de Bruin]

Hi craigh

where a certain cell in that row is blank

In a specific column? or if one cell in the column A:Z is empty?

Please more information

 

[craigh]

Thank you, Ron de Bruin for your reply. It is column E but later in the
Excel doc it is column F. If cell in those columns are blank, then I
want to delete, hide or suppress the row the cell is in.

Any help will be greatly appreciated.

Thanks!
Craig

 

[Ron de Bruin]

One way

This will work for row 1 till 100
For more examples see
http://www.rondebruin.nl/delete.htm

If you want to hide use
..Rows(Lrow).Hidden = True


Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = EndRow To StartRow Step -1

If Trim(.Cells(Lrow, "E").Value) = "" Or _
Trim(.Cells(Lrow, "F").Value) = "" Then .Rows(Lrow).Delete

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

 

[craigh]

Ron de Bruin,

This is very much what I want! Thank you so much for your reply.

I actually have two command buttons. One will use your code, and th
other one will unsuppress the rows. So, is there anothe
function/method instead of "delete" that could be used? If yes, wha
would the code look like for both command buttons?

Thanks alot!
Crai

 

[Ron de Bruin]

If I understand you correct?

If you use the hide line instead of the delete line in the macro

.Rows(Lrow).Hidden = True

Then your other macro can use this to unhide

Sub test()
With UsedRange
.Columns.Hidden = False
.Rows.Hidden = False
End With
End Sub

 

[craigh]

Ron de Bruin,

THANK YOU! That worked and did exactly what I wanted. You ar
definitely an expert with Excel and VBA.

Thank you!
Crai