Problem with macro

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
What should be left afterwards?
 

Some videos you may like

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
Joined
May 13, 2020
Messages
42
Office Version
365
Platform
Windows
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
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
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

New Member
Joined
May 13, 2020
Messages
42
Office Version
365
Platform
Windows
Almost perfect :biggrin:
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
Joined
May 13, 2020
Messages
42
Office Version
365
Platform
Windows
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
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
I assumed that was a header row & should be left, change this
Rich (BB code):
For r = lr To 1 Step -1
 

Jeeremy7

New Member
Joined
May 13, 2020
Messages
42
Office Version
365
Platform
Windows
I'm creating a header afterward

Thanks again that solved it :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,921
Office Version
2007
Platform
Windows
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

New Member
Joined
May 13, 2020
Messages
42
Office Version
365
Platform
Windows
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 ! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,100,033
Messages
5,472,105
Members
406,802
Latest member
Jakub3

This Week's Hot Topics

Top