Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Suppressing Blank Rows | Excel Discussion (Misc queries) | |||
Suppressing the top grid line of a cell - 2007 Excel | Excel Discussion (Misc queries) | |||
Suppressing detail rows | Excel Discussion (Misc queries) | |||
Finding a value and then suppressing ALL rows WITHOUT the found va | Setting up and Configuration of Excel | |||
add end rows after suppressing prior rows | Excel Programming |