Problem with macro

Jeeremy7

New Member
Joined
May 13, 2020
Messages
42
Office Version
365
Platform
Windows
Hello,

I have a macro running but i think some informations are in double so the macro doesn't work..
If somoene could help it would be really nice :)

Here's the code
VBA Code:
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = lr To 1 Step -1
        If (Cells(r, "B") Like "") Then
            If Rng Is Nothing Then Set Rng = Rows(r) Else Set Rng = Union(Rng, Rows(r))
        End If

    lr = Cells(Rows.Count, "A").End(xlUp).Row
        If 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
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True
Thanks ! :)
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
In what way doesn't it work?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,924
Office Version
2007
Platform
Windows
What do you mean by Cells (r, "B") Like "", that is, if the cell is empty?

What do you mean by Cells (r, 1) Like "## - ####", that is, if the cell contains the text "## - ####" or the cell has a number like "12 -3456"?
 

Jeeremy7

New Member
Joined
May 13, 2020
Messages
42
Office Version
365
Platform
Windows
Fluff, it's telling me Run time error 13 Type mismatch

Dante, I want to delete all row with an empty value in column B and then delete all cell in column A that don't have let's say 12-1234 or 23-4567

Thanks !!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
Check that you don't have any error values (like #N/A, #VALUE,#NAME etc) in cols A & B.
 

Jeeremy7

New Member
Joined
May 13, 2020
Messages
42
Office Version
365
Platform
Windows
Check that you don't have any error values (like #N/A, #VALUE,#NAME etc) in cols A & B.
Yes I have a #NAME in column A that's why i created the formula to delete lines in column B first that would indirectly delete the rows with the #NAME values in A.
But I can't figure a way to make the second one to work ..

I hope you understood my poor explanation .. haha
 

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 1 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))
        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
Ok, try
VBA Code:
lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = lr To 1 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))
        End If
    Next r
    If Not Rng Is Nothing Then Rng.Delete
No error but I still have all the cells in A. The final goal would be to just have ##-### number in column A
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
Do you have values in col A other than #NAME & ##-####
 

Jeeremy7

New Member
Joined
May 13, 2020
Messages
42
Office Version
365
Platform
Windows
This is what it looks like just before the error
Copy of Copy of TEMPLATE - Lump Sum Direct cost.xlsm
ABC
1-Amount P.O.#
2-
3-
4L.- MAZ540.49
5L.- MAZ528.76
6L.- MAZ712.85
7L.- MAZ697.36
8L.- MAZ509.2
9T.- HIC571.56
10L.- MAZ160.41
11T.- HIC114.37
1201-01023835
13-
14-
15e -de D495
1601-0121495
17-
18-
19 M-ob (26.73
20 M-ob (8.23
21 M-ob (5.49
22 M-ob (17.13
2301-013757.58
24-
25-
26ig-ital5
2701-52015
28-
29-
30ne-rs o425
3102-7403425
32-
33-
34 B-uild34.04
35 B-uild91.8
3606-1101125.84
37-
38-
39nt-age 1743
4008-11011743
41#NAME?
Direct Cost
 

Watch MrExcel Video

Forum statistics

Threads
1,100,042
Messages
5,472,142
Members
406,806
Latest member
babarfirasat

This Week's Hot Topics

Top