Deleting rows in two columns with a code in visual basic

[Ruben]

Hello,

I have a problem with making a code in visual basic. I would like to make a
code that deletes all rows in which the value of column G starts with a ‘W’
(after ‘W’ there is a number, example: W-123-231). I can do this, but now
comes the most difficult thing.
All my rows have numbers in column C, varying from 100 till 1000 (my total
file is about 5000 rows). Some rows have the same number in column C (because
they are related to each other: same kind of product). Because I would like
to delete all rows for which the first value in column G is a ‘W’, I also
would like to delete all rows that are related to the rows with a ‘W’ in
column G. So for the row which value in column G starts with a ‘W’, all
related rows should be deleted as well. This means that all rows with the
same number in column C as the row with a ‘W’ in the G column, have to be
deleted.
So, in summary, all rows that start with a `W´ in column G and the related
rows (which have the same number in column C) have to be deleted.
I don’t know how I can put this in a code in visual basic, so it would be
great if someone could tell me! I hope my problem is clear, if it is not let
me know. Thank you very much for helping me!

 

[Don Guillett]

try this>select the title row(row1) >data>filter>autofilter>custom>begins
with w

abbbbb
ddd
wass
ddd
wass
ddd
wass
ddd
wass
ddd
wass
ddd
wass
ddd
wass

 

[Joel]

The best way of doing this is with a Sumproductt worksheet formula in an
auxilary column.

I would first get it right using manually entered formulas and then convert
to a macro

the line below if put in Row D in an auxilary column wil put 1 if C10
matched the number in Column A and a W proceeded the number
=Sumproduct(--(left(A1:A100,1)="W"),--(mid(A1:A100,2)=C10))

You need an OR statment to get your complete solution

=Sumproduct(--(left(A1:A100,1)="W")+Sumproduct(--(left(A1:A100,1)="W"),--(mid(A1:A100,2)=C10))


The VBa for this will be something like this
A_RANGE = "A1:A100"
for RowCount = 1 to 100
Range("X" & RowCount).formula = "=Sumproduct(--(left(" & A_RANGE & _
",1)=""W"")+Sumproduct(--(left(" & A_RANGE & ",1)=""W"")," & _
"--(mid(" & A_RANGE & ",2)=C" & RowCount & "))"
next RowCount

 

[Sheeloo]

Here is the logic I think will take least amount of time;
Sort on Column C
Pick the first value in Column C
Loop till value in C changes
Check whether Column G starts with a W
if Yes then Mark all rows with same C for deletion from beginning till C
changes

Let me know if you want the code also. You can send the file to me at
(e-mail address removed). I will put in the code and send to you within 24
hours.

 

[Don Guillett]

A re-read suggests that you either need to have a unique list or use a macro
to create a unique list.
Then a for each loop for each item in the list to look for duplicates that
start with w. Doable

 

[Ruben]

It works now, so thanks for all your helpful reactions!

Ruben