Problem with macro

Jeeremy7

Board Regular
Joined
May 13, 2020
Messages
110
Office Version
  1. 365
Platform
  1. 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 ! :)
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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"?
 
Upvote 0
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 !!
 
Upvote 0
Check that you don't have any error values (like #N/A, #VALUE,#NAME etc) in cols A & B.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Do you have values in col A other than #NAME & ##-####
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,743
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top