Using LastCell value in VBA code

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

Good Morning.

Using Office 2003

I am trying to:

1) Locate the last cell imported data.
2) Copy a formula in G2 for all G cells to the end of data (G1 contains field names)

To get the last cell of data, I used Edit>GoTo>Special>Last Cell

In the code below, I attempted to insert the value of the last cell into a
Range("G1:xxxxx).Select to do a paste of G2.formula.

My attempt did not work.

I am sure there is a better way.

Help please! TIA

Dennis

Sub FindLastCell() ' Partial code below

[OK] Selection.SpecialCells(xlCellTypeLastCell).Select
[Bad] Range("G3:" & Selection.SpecialCells(xlCellTypeLastCell).Select).Select

End Sub
 
One way:

Range("G3", Selection.SpecialCells(xlCellTypeLastCell)).Select

When you used the &, the compiler interprets this as wanting to
concatenate "G3:" and the default property of the last cell (i.e., the
value), and select that range. Unless the last cell contains a valid
cell address, it will throw an error.

Note that you almost never need to select *any* range - using the range
objects directly make for smaller, faster, and, IMO, more easily
maintained code. For instance, to copy the formula in G2 to the last
filled cell in column G:

Range("G2").AutoFill _
Destination:=Range("G2:G" & _
Range("A" & Rows.Count).End(xlUp).Row), _
Type:=xlFillCopy



Note that this assumes that the cell in column A will be filled for the
last row with data. Pick a different column if desired.
 
As you already know, it works fine!

Thanks for your time to also explain why. I really am trying to learn how to "fish"
so that I have no need beg for "food."

Dennis
 
Back
Top