Solved: Delete part of a row based on a empty cell

[Riaaz66]

Hi,

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?

Thanks,

RM

[Bob Phillips]

[vba]

'-----------------------------------------------------------------
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))
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

'tidy up by deleting the column we added
.Columns(TEST_COLUMN).Delete
End With
End Sub
[/vba]

[Riaaz66]

Hi Xld,

I get stuck on this part

[VBA]
Columns(TEST_COLUMN).AutoFilter Field:=1, Criteria1:="=", Operator:=xlAnd
[/VBA]

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?

Regards,

RM

[Riaaz66]

Hey,

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?

Regards,

RM

[Aussiebear]

Change Test-Column to "A"

[Riaaz66]

Hi, It still removes the complete first column. Which is not supposed to happen. I replaced "TEST_COLUMN" with "A"