formatting cells

[dan graziano]

Hi,

I would appreciate it if you could tell me how to do the VB code for
these:

1) Start at row 1. Go to column 2 and check if it contains the words
"etc". If it does have these words, blank out the cell to make it
empty. If it doesn`t have these words, do nothing. Do this for every
row of the dataset which contains data.

2) Go through a data set and delete all rows which are duplicates. A
duplicate is when a row`s contents in columns 1,2 and 3 are identical to
that in columns 1,2 and 3 of another row.

Thanks.

Dan

 

[Tom Ogilvy]

1)
Dim rng as Range
set rng = Range(Cells(1,2),Cells(rows.count,2).End(xlup))
for each cell in rng
if instr(1,cell,"etc",vbTextCompare) > 0 then
cell.clearcontents
end if
Next

2)
are the rows sorted so that any duplicates would be next to each other?

 

[dan graziano]

Hi Tom,

Thanks for the code for the first question.

The positioning of the duplicate rows may be very random, there is no
pattern to their positioning. It`s a very large dataset, and it`s
possible that duplicate rows are not next to each other (ie. they may be
rows 1 and rows 5000). Or it`s also possible that duplicate rows are
next to each other (ie. they may be rows 1 and 2).

Thanks.

Dan