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

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

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