Examples of VBA code to scan all rows and perform a function conditionally?

[pjfarley3]

Hi all,

I have a need to scan all or some rows of an excel sheet, stopping at
rows that satisfy some condition (e.g., cell in column D = "some
text"). At each such row, I need to perform a function that will
modify the row as needed. Sometimes cells in the selected rows will be
modified, sometimes the row will be deleted.

As a separate task (probably best as a simple keystroke macro, I would
guess) I also need to be able to import additional rows to the "end" of
the sheet from an external file each month, probably in csv format
generated from a non-excel application. Then I will need to run the
"scan" program on the new rows to integrate them into the sheet.

Is there any example code out there that has this general scheme? I
have been looking at various code example sites, but none that I have
found so far have code to scan across all rows.

TIA for any urls/info/RTFM's you can provide.

Peter Farley

 

[Guest]

dim Rng as Range
dim oCell as Range
dim LastRow as Long
dim J as long

LastRow= Worksheets("Sheet1").Range("D65536").End(xlUp).row
Set Rng = Worksheets("Sheet1").Range("D2").Resize(1,LastRow-1)

For J = LastRow to 2 Step -1
If Rng(J-1).Value = "Some Text" Then
Rng(J-1).EntireRow.Delete
Elseif Rng(J-1).Value = "Other Text" Then

 

[Guest]

Does this work for you?


dim Rng as Range
dim J as long
dim LastRow as Long

LastRow = Worksheets("Sheet1").Range("D65536").End(xlUp).Row
Set Rng = Worksheets("Sheet1").Range("D2").Resize(1,LastRow-1)

For J = LastRow to 2 Step -1
If Rng(J-1).Value = "Some Text" Then
Rng(J-1).EntireRow.Delete
ElseIf Rng(J-1).Value = "Other Text" Then
Rng(J-1).Offset(0,3).Interior.ColorIndex = 35 'or whatever you
want to do
Else Rng(J-1).Offset(0,-1) =Rng(J-1).Offset(0,-1) * 1.05 'something
else to do
Next J

End Sub

 

[pjfarley3]

Yes, that is a good start. However, I will need to modify more than
just one column. Do I have to have a "Range" variable for each column,
or can the range span multiple columns (say, A-H)? And if the range is
multi-column, how do I refer to specific cells in the selected row?

Thank you very much for the clear example, it is very helpful.

Peter

 

[Guest]

Peter,
Sure the lookup range can always be modified, but one must keep in mind that
the code is extremely specific. That is, it does one thing very well but can
never just guess what the dev wants. Hence, you will need to tell us exactly
what it is that you want the code to do:

Up to here my code looks for some text in Col D. If you want more, please
tell us what columns to search in, what to search for, what to do if it
finds x or y or z, and under what conditions.

 

[Guest]

As for appending new data at the end of the sheet:

I would explore Data>Get External Data. Also, since the format is CVS I
would import into a new worksheet, get it correctly formated to match your
database, then
copy it to the db sheet.

 

[pjfarley3]

OK, let me get a little more specific. I have a CSV file generated by
another application that I will process with Excel. For each row that
does NOT have specific text in column D, make the column J cell into a
given formula (simple addition and subtraction of neighboring cells
only).

Some lines have a specific text value in column D that tells me to
examine the immediately following rows, but only up to the next row
that is non-empty in column A.

For each row following the row with specific text in column D, I must
move the contents of column B to column D, and the contents of column F
to column I. Then I must copy the contents of the other columns (other
than D, I and J) from the preceding row with the specific text in D
into the current row. Lastly I must put the same formula as all other
rows into column J. J is the last column in the sheet.

It is guaranteed that there are always at least two (possibly more)
secondary rows to process, or the specific value would not be in column
D.

One extra step will be required for the move from column B to D. The
value in B must be used as a search value in another Excel file sheet
to select a value from a two-column table. The selected value becomes
the contents of column D. The search key is in column F of the
separate sheet and the replacement value is in column B.

Then I move to the next row, and if it has an empty column A cell,
repeat the process.

When I find the next row with non-empty column A, delete the row with
the specific text that was found to start the process, and start
looking for specific text in column D again.

After I first establish the spreadsheet from the initial CSV file, I
will have monthly additions of new rows in CSV files, which need to be
added to the end of the current sheet and then processed in the same
way as all the prior rows to integrate them into the spreadsheet.

I don't think the low volume of new data each month will require
splitting the single sheet into more than one sheet. I will just keep
one continuously growing sheet for now. The initial CSV file has only
a few hundred rows, and I would expect less than 50 new rows each
month.

Is that clear enough?

Thanks a lot for your patience and persistence with my ignorance.
Muchly appreciated.

Peter