Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank cells
i have a spreadsheet about 11 columns wide and 10,000rows down.
i want to delete all rows that have columns H, I AND J blank. IF any one column out of the 3 are blank i do not want the row to be deleted. The row is deleted only when the Columns H, I and J are blank in the same row. please advise |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank cells
try the following..... it use column 1 to determine the last used row.
Sub DeleteHIJ() Dim xlr As Long, xr As Long xlr = Cells(Rows.Count, 1).End(xlUp).Row ' change this if another column contains the last used row For xr = xlr To 1 Step -1 If Len(Cells(xr, 8)) = 0 And Len(Cells(xr, 9)) = 0 And Len(Cells(xr, 10)) = 0 Then Rows(xr).Delete End If Next xr End Sub -- Cheers Nigel "savbci" wrote in message ... i have a spreadsheet about 11 columns wide and 10,000rows down. i want to delete all rows that have columns H, I AND J blank. IF any one column out of the 3 are blank i do not want the row to be deleted. The row is deleted only when the Columns H, I and J are blank in the same row. please advise |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank cells
If you are not comfortable with macros do this:
Put auto filter. For each of the columns H I and J select "Blank cells " from the filter dropdown menu. Do this for all the 3 columns and you will have all those rows where there is no data in these three columns. Now select all the visible cells in any one of these columns till the last non blank row (say row no. 10,000). To select visible cells - Ctrl G, Alt S and in the resulting screen select "visible cells only" and click OK. Now right click and select "delete row" when promoted -"delete entire row?" click yes. Alternatively do Ctrl -.and then delete entire row when prompted. Remove the auto filter and your job is done. Thanks Karthik Bhat Bangalore |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting blank cells globally | Excel Discussion (Misc queries) | |||
Deleting spaces in blank cells | Excel Discussion (Misc queries) | |||
Deleting blank spaces in cells | Excel Discussion (Misc queries) | |||
Deleting blank cells in a column | Excel Discussion (Misc queries) | |||
Deleting rows with blank cells | Excel Worksheet Functions |