View Full Version : Solved: Delete part of a row based on a empty cell

10-07-2010, 07:37 AM

I need to have VBA code that checks if there is no value in column A.(like cell A4 is empty in this example - see attachment). If it is empty the row should be deleted from the range A4:E4.
Is this possible to do in a loop untill row 10000 has been reached?



Bob Phillips
10-07-2010, 07:55 AM
Public Sub DeleteDuplicateRowsUsingAutofilter()
' Function: Conditionally remove rows
' Synopsis: Applies Autofilter to results
' Deletes visible rows
' Note: Change the 'TEST_COLUMN' variable if the data to
' be checked for uniqueness is not in column 1
' Author: Bob Phillips - 2008 xlDynamic.com
Const TEST_COLUMN As Long = 1
Dim Lastrow As Long

With ActiveSheet

'first, count the rows to operate on
Lastrow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

'finally, apply the autofilter for al matching cells
Columns(TEST_COLUMN).AutoFilter Field:=1, Criteria1:="=", Operator:=xlAnd

'we now have only matching rows visible, so we can
'delete these matching rows
With .Range(.Cells(2, TEST_COLUMN), .Cells(Lastrow + 1, TEST_COLUMN))
End With

'tidy up by deleting the column we added
End With
End Sub

10-08-2010, 12:26 AM
Hi Xld,

I get stuck on this part

Columns(TEST_COLUMN).AutoFilter Field:=1, Criteria1:="=", Operator:=xlAnd

The error message is:
Run-time error '1004'
The command could not be completed by using the range specified. Select a single cell within the range and try the command again.

Now, I do select cell A2, before I run the code.
Besides that, what does the "criteria" part mean, does it mean that it filters where the value is "=" ?

Please help?



10-08-2010, 01:34 AM

I don't want to be rude, but despite the fact, that the provided code contains a description for each action, I think that I didn't explain my problem correctly.

In the example sheet I also described what the code should do.

Basically this is the situation:
In a sheet, column A till E contains data. Column A can ocassionally be empty but column B till E always contains data. There are also empty rows (column A till E)
If the value of the cell in column A is empty, then the row should be deleted, and the rows below should be shifted up, so that it will not cause empty rows.

I'm actually expecting a loop kind of formula.

Can somebody help me?



10-08-2010, 04:22 AM
Change Test-Column to "A"

10-10-2010, 10:25 AM
Hi, It still removes the complete first column. Which is not supposed to happen. I replaced "TEST_COLUMN" with "A"