Suppressing rows where a cell is blank

C

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
 
C

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
 
R

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
 
C

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
 
R

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
Click to expand...

Then your other macro can use this to unhide

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

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
 
Top