Remove Customers Starting With an "A"

wayneshirley

Board Regular
Joined
Jun 23, 2003
Messages
140
Hi there,

I need some code to add to a macro that will delete customer account names that start with an "A" in column C. Specifically it needs to find customer names starting with the letter "A" and delete that entire row.

Can anyone help me please?

Thank you.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Errrr, can you sort column C and delete all the A's manually? Or is this something you'll have to do often?
 
Upvote 0
If for some reason you are prevented from sorting then you could alternatively use Autofilter with a custom filter of "Begins With" and "A", then delete the records returned. In fact, you could use this method to record a macro performing these actions which will give you the code you require.
 
Upvote 0
Hi Steve,

Yes, I asked how to COPY all accounts starting with an "A" in column C on Sheet 1 to Sheet 2. The code you provided worked fine.

What I should have asked was how to CUT (not copy). So I either need amended code or additional code that will delete "A" accounts from sheet 1.

Are you able to help please?

Thank you.
 
Upvote 0
Range("c65536").End(xlUp).Select


While ActiveCell.Row > 2
If Left(ActiveCell, 1) = "A" Then

ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(-1, 0).Select
Wend
 
Upvote 0
Hi Steve,

Thank you for that piece of code, it worked perfectly!

Now that I have started using the macro (new code included) I find that I really needed code that would copy customers starting with the letter A plus any customers starting with a numeral. The code I have fitted into my macro is as follows.

Code:
With Sheets("VL")
With .Range("C1", .Range("C" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="=A*"
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Sheets("SC").Range("B2")
.AutoFilter
End With
End With

The same applies for deleting customers stating with A and also starting with a numeral.

Are you able to help me again please?

Thank you so much!
 
Upvote 0
Something along the lines of

Dim c As Range, rng
Columns("C:C").Select
lastrow = Cells(65536, Selection.Column).End(xlUp).Row

Set rng = Range("c2:c" & lastrow)
For Each c In rng
c.Offset(0, 1).FormulaR1C1 = "=CODE(LEFT(RC[-1],1))"
If c.Offset(0, 1).Value > 48 And c.Offset(0, 1).Value < 58 Or c.Offset(0, 1).Value = 65 Then
c.Offset(0, 2) = "Delete"
End If

Next c

Range("e65536").End(xlUp).Select


While ActiveCell.Row > 1
If ActiveCell.Value = "Delete" Then

ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(-1, 0).Select
Wend
 
Upvote 0
Range("c65536").End(xlUp).Select


While ActiveCell.Row > 2
If Left(ActiveCell, 1) = "A" Then

ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(-1, 0).Select
Wend


I used this same line of code to delete rows that had "ND" in column B like so:

Sheets("Datos").Select
Range("b65536").End(xlUp).Select
While ActiveCell.Row > 2
If Left(ActiveCell, 1) = "ND" Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(-1, 0).Select
Wend
End Sub

The macro does something, sort cells, screen jitters, etc, no error messages come up, but it doesn´t delete ANYTHING, and I checked the data and it has 5 rows with ND on column B that should have being gone by the end of the macro

What am I doing wrong?
 
Upvote 0
Hi

You need to specify two characters to be extracted using the Left function:

Rich (BB code):
If Left(ActiveCell, 2) = "ND" Then
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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