Suppressing rows where a cell is blank
 

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


---
Message posted from http://www.ExcelForum.com/

Suppressing rows where a cell is blank
 

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"craigh " wrote in message ...
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


---
Message posted from http://www.ExcelForum.com/

Suppressing rows where a cell is blank
 

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


---
Message posted from http://www.ExcelForum.com/

Suppressing rows where a cell is blank
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"craigh " wrote in message ...
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


---
Message posted from http://www.ExcelForum.com/

Suppressing rows where a cell is blank
 

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

--
Message posted from http://www.ExcelForum.com

Suppressing rows where a cell is blank
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"craigh " wrote in message ...
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 the
other one will unsuppress the rows. So, is there another
function/method instead of "delete" that could be used? If yes, what
would the code look like for both command buttons?

Thanks alot!
Craig


---
Message posted from http://www.ExcelForum.com/

Suppressing rows where a cell is blank
 

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

--
Message posted from http://www.ExcelForum.com