Using Range() Property With Do While Loop

Grant1377 · Jul 10th 2012

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:

Code

Dim RowCount As Long
Dim x As Long
x = 0
RowCount = 1
'x is variable that will end the loop
Sheets("Sheet1").Select
    
Do While x = 0
    If Cells(RowCount, 1) = "" Then
            x = 1
            'Ends the loop.  We reached a row with no value
        Else
            RowCount = RowCount + 1
            'There's a value.  Move to the next row
        End If
    
    Loop
'After the loop, RowCount should equal the bottom row of the value.  Now, we want to copy rows 1 through (RowCount - 1) and insert them into another sheet


' I need to copy the rows from 1 to (RowCount - 1), however I can't figure out how to use the Range() property combined with the variable RowCount to selec the range I need to copy.  I've tried using the Cells property but cannot Select a range of cells to copy with that property.

Display More

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

Thanks!

shane_wilson09 · Jul 10th 2012

Re: Using Range() Property With Do While Loop

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

Try:

Range("A1:E" &RowCount)

Richie(UK) · Jul 10th 2012

Re: Using Range() Property With Do While Loop

Hi and welcome.

Is this the sort of thing that you mean?

Code

Sub test()
    Dim lRow As Long
    
    lRow = 123
    MsgBox Range("A1:E" & lRow).Address
    'or
    MsgBox Range(Cells(1, "A"), Cells(lRow, "E")).Address
    
End Sub

Grant1377 · Jul 10th 2012

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?

Richie(UK) · Jul 10th 2012

Re: Using Range() Property With Do While Loop

Hi,

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

Using Range() Property With Do While Loop

Participate now!

Share