# Problem with macro

#### Fluff

##### MrExcel MVP, Moderator
What should be left afterwards?

• Jeeremy7

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Jeeremy7

##### New Member
What should be left afterwards?
Just this information

Copy of Copy of TEMPLATE - Lump Sum Direct cost.xlsm
AB
101-01023835
201-0121495
301-013757.58
401-52015
502-7403425
606-1101125.84
708-11011743
Direct Cost

#### Fluff

##### MrExcel MVP, Moderator
Ok, try
VBA Code:
``````lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
If Cells(r, "B") = "" Then
If Rng Is Nothing Then Set Rng = Rows(r) Else Set Rng = Union(Rng, Rows(r))
End If

If IsError(Cells(r, 1)) Then
If Rng Is Nothing Then Set Rng = Rows(r) Else Set Rng = Union(Rng, Rows(r))
ElseIf Not Cells(r, 1) Like "##-####" Then
If Rng Is Nothing Then Set Rng = Rows(r) Else Set Rng = Union(Rng, Rows(r))
End If
Next r
If Not Rng Is Nothing Then Rng.Delete``````

• Jeeremy7

#### Jeeremy7

##### New Member
Almost perfect I just have a value that stayed i'm not sure why (row1)

Copy of Copy of TEMPLATE - Lump Sum Direct cost.xlsm
ABC
1-Amount P.O.#
201-01023835
301-0121495
401-013757.58
501-52015
602-7403425
706-1101125.84
808-11011743
908-1102303.75
1008-7002149.11
1109-200313900
1209-650311100
1309-91034600
1421-13031294.44
1523-300311314
1626-60039658.6
Direct Cost

#### Jeeremy7

##### New Member
It's ok I pulled differents data and this row always stays I'll just create a .delete for A

Thanks a lot for you help Fluff it's not the first time and it's always answering the question !

#### Fluff

##### MrExcel MVP, Moderator
I assumed that was a header row & should be left, change this
Rich (BB code):
``For r = lr To 1 Step -1``

• Jeeremy7

#### Jeeremy7

##### New Member

Thanks again that solved it #### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

• Jeeremy7

#### DanteAmor

##### Well-known Member
I think it could be simplified a bit like this:

VBA Code:
``````Sub test()
Dim lr As Long, r As Long, rng As Range
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Rows(lr + 1)
For r = lr To 1 Step -1
If IsError(Cells(r, 1)) Then
Set rng = Union(rng, Rows(r))
ElseIf Cells(r, "B") = "" Or IsError(Cells(r, 1)) Or Not Cells(r, 1) Like "##-####" Then
Set rng = Union(rng, Rows(r))
End If
Next r
rng.Delete
End Sub``````

• Jeeremy7

#### Jeeremy7

##### New Member
I think it could be simplified a bit like this:

VBA Code:
``````Sub test()
Dim lr As Long, r As Long, rng As Range
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Rows(lr + 1)
For r = lr To 1 Step -1
If IsError(Cells(r, 1)) Then
Set rng = Union(rng, Rows(r))
ElseIf Cells(r, "B") = "" Or IsError(Cells(r, 1)) Or Not Cells(r, 1) Like "##-####" Then
Set rng = Union(rng, Rows(r))
End If
Next r
rng.Delete
End Sub``````
That's working too thanks ! • DanteAmor