repeating Macro on selected rows

aboo · #1 (**permalink**) 05-30-06, 20:11

Hi,

Guys i need your help. Im a newbie to this and thank anyone who gives my post a look see.

I have created a macro to run on a certain row, i have created it in the 'relative' mode as to run it on differnt rows dependant on the active cell.

I have a spreadsheet in which the rows of data are variable. For example i can have 500 rows of data one week and 1000 the next.

What i want to do is repeat the macro on all the lines with data in them.

I know that it is a For loop i need to create however i am not fully versed in VBA.

here is a example

A ------B---------C--------D

1 ------jo--------pete

2 ------bill-------jon

3 <<<< no data on this row

4 -----elli-------jim

I run a macro which does soemthing to D column next to the data. I want to repeat the macro on each row that contains data in the C column. C3 is blank so i dont want the macro to be run on D3.

Can anybody help me please.

Thankyou.

shades · #2 (**permalink**) 05-31-06, 00:44

Howdy. You can use an If Then statement, checking whether the cell in Col is blank.

Code:

Sub test()
    Dim lngRow As Long
    Dim lngLastRow As Long
    lngLastRow = Range("D65356").End(xlUp).Row
    
    For lngRow = 2 To lngLastRow
    
        If Range("C" & lngRow).Value <> "" Then Range("D" & lngRow).Value = Range("D" & lngRow).Value + 1
    Next lngRow
End Sub

aboo · #3 (**permalink**) 05-31-06, 05:44

Ty for replyin shades this has pointed me in the right directions., is it possible for you to put comments on the lines as i do not understand the function of .End(xlUp).Row

Thank you for your time once again.

aboo

shades · #4 (**permalink**) 05-31-06, 19:50

Rather than go through all 65536 rows to do what you want, the use of End(xlUp) finds the last used row in a specific column. In this case, in Column D, it goes to the absolute last row [65536], then goes up from there until it finds data of some kind. Thus, the code works until it hits the last row of data. Since you have varying number of rows each time it is, run, this code will always work, no matter how many or how few rows you have.

Actually, in preparation for XL 2007, it would be better to not use 65536 hard code, and use Rows.Count, which will work even if there are 1,000,000+ rows (as in XL2007).

aboo · #5 (**permalink**) 06-01-06, 16:23

Thankyou Shades, your a star!

Internet Services

Web Development

Digital Marketing

Consumer Tech