Full Version: Trying to usa Range in VBA
UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel
dnohr
Apr 19 2007, 04:22 PM
This seems like it would be simple enough, but I can't seem to find an answer anywhere.

I have a formula worked out that shows the last row in shreadsheet.

I then want to you this number in some VBA code.
Say the cell with the last row number is B1, what's the proper want to say (in VBA)

Range("A2:A**Row number in A1**").Select


thanks!
KingMartin
Apr 20 2007, 12:34 AM
Range("A2:A" & Range("A1")).Select

or without using the formula:

CODE
With Sheets("Sheet1")
    .Range("A2:A" & .Cells(.Rows.count,"A").End(3).Row).Select
End With


1) Just curious - what is the formula?

2) Why do you need to select?

Martin
dnohr
Apr 23 2007, 05:33 PM
Maybe I don't need to select, maybe I'm blindly just trying to repeat how I do it manually now.

I have a row of formulas that I need to copy down to many other rows. The data is imported, so the number of rows is different every time I do it. How i do it now it copy the cells in the top row, then select all the rows below and click paste. I figured out how to find the last row of data, but I haven't figured out how to paste data from row 7 down to whatever row is the last.
KingMartin
Apr 24 2007, 12:03 AM
It is safe to assume that, for example, your first row has data and formulas in A7:E7 and you want to copy these formulas down along data in column A? For example, into B2:E100, if the last entry is in A100?

Martin
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.