Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Posts
    178

    Unanswered: repeating Macro on selected rows

    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.
    Last edited by aboo; 05-30-06 at 21:19.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    May 2006
    Posts
    178
    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

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    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).
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    May 2006
    Posts
    178
    Thankyou Shades, your a star!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •