Using Range() Property With Do While Loop

  • Is it possible to use the Range() property with a variable as an input? I'm trying to use it with fixed columns but variable rows.

    Essentially, I'm trying to copy rows with values from one sheet and insert them into another. The number of rows with values that I'm copying varies. Here's my code:

    Range("A"1:"E"RowCount) obviously does not work...


  • Re: Using Range() Property With Do While Loop

    I actually posted a similar question a few days ago. Happy to pass on the advice!


    Range("A1:E" &RowCount)

  • Re: Using Range() Property With Do While Loop

    Hi and welcome.

    Is this the sort of thing that you mean?

    Sub test()
        Dim lRow As Long
        lRow = 123
        MsgBox Range("A1:E" & lRow).Address
        MsgBox Range(Cells(1, "A"), Cells(lRow, "E")).Address
    End Sub
  • Re: Using Range() Property With Do While Loop

    Thank you both very much for the quick help! Exactly what I was looking for.

    It would be nice if there were a program somewhere that has all Excel VBA objects, properties and functions in it with their potential inputs/parameters. That way I could avoid these problems in the future. Excel has something similar built in but it isn't too helpful and I've looked a bit online but came up empty. Do either know of a similar program or online resource?

  • Re: Using Range() Property With Do While Loop


    You could try buying a book like "Excel 2007 VBA Programmer's Reference". If that isn't an option then just keep writing code, use F1 to use the in-built help, and frequent forums such as this one (you don't have to post solutions if you don't want to but think about how you would tackle a problem and then compare that with what others suggest). Oh, and don't forget to make use of the macro recorder - you will often need to tidy up its efforts but it will point you in the right direction. Keep at it :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!