Syntax help in defining a range 1

[srogers]

I know the first cell I want for my range (A2).
The last column will be F.
The longest Column is A so I'm finding the last cell address in column A.

Can I separate the row number from the column letter (I don't need A579, I just need 579) and use a cell reference combined with a variable name in defining a range?

Sub LastCellInColumn()
Dim LastCell As String

Range("A65536&quot.End(xlUp).Select
LastCell = Range("A65536&quot.End(xlUp).Address
'want something like: MyRange = A2:F(LastCellRow#)
End Sub

Thank you -

 

[SkipVought]

Hi,

Try this

Sub LastCellInColumn()
    Dim LastCell As Long

    Range("A65536").End(xlUp).Select

    LastCell = Cells(Cells.Rows.Count, 1).End(xlUp).Row
    Set MyRange = Range(Cells(1, 1), Cells(LastCell, "F"))
End Sub

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

I failed to remove part of your code...

Sub LastCellInColumn()
    Dim LastCell As Long
    LastCell = Cells(Cells.Rows.Count, 1).End(xlUp).Row
    Set MyRange = Range(Cells(1, 1), Cells(LastCell, "F"))
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[srogers]

Thanks Skip.

I see that LastCell now returns the row number.
2 questions:

1. If I want to clearcontents on the range A2:F(LastCell)and I try this:
Worksheets("GL_Data&quot.Range("A2:F(LastCell)&quot.ClearContents
I get application or object-defined error.
Can I use the variable LastCell in the range definition?

2. In order to better understand the code you provided:
LastCell = Cells(Cells.Rows.Count, 1).End(xlUp).Row
Set MyRange = Range(Cells(1, 1), Cells(LastCell, "F&quot)

What would you suggest I search on? Cell objects? Row Method?

Thanks for your help.

 

[SkipVought]

Worksheets("GL_Data").Range("A2:F" & lastcell).ClearContents

I'd rather use...

Worksheets("GL_Data").Range(Cells(2, 1), Cells(lastcell, "F")).ClearContents

'cuz I'm lazy about counting to F.

Look at Range, 'cuz alot happens with ranges in Excel.
Also Cells.

Cells referrs to ALL cells. So Cells.Rows.Count is the sheet row count. Cells.Columns.Count is the sheet column count.

We defined MyRange as Set MyRange = Range(Cells(1, 1), Cells(LastCell, "F&quot). Check this out...

With MyRange
  FirstRow = .Row
  LastRow = FirstRow + .Rows.Count - 1
End With

Then lots can be done with Collections -- look that one up too!

For Each c In MyRange
  MsgBox c.Value
Next

Hope this helps




Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[srogers]

Thanks Skip - it helped to look up Range Object which described the Cell property.

Another question - if you don't mind.

I cleared the contents and it worked.

Then I filled in some more data omitting column A.
This time A1 thru F2 were deleted. So apparently something in the next two lines is relative?

I'm guessing it is either in this:
LastCell = Cells(Cells.Rows.Count, 1).End(xlUp).Row
or this:
Worksheets("GL_Data&quot.Range(Cells(2, 1), Cells(LastCell, "F&quot).ClearContents

I'm not sure I understand the meaning of Row Index and Column Index (apparently 2, 1 isn't always row 2 column 1?).

Thank you

 

[SkipVought]

THis statement...

LastCell = Cells(Cells.Rows.Count, 1).End(xlUp).Row

is looking at COLUMN A and doing an END UP, which you can do on your keyboard by

1. selecting the very last row in column A

2. [End] key

3. [Up] arrow

If you have deleted some data in column A then this process will end up on a different row. It all depends on your data and what you are trying to accomplish.

Try looking at the CurrentRegion property and the UsedRange property.

Here's a way to find the last used row by analyzing all the columns od data on a sheet, with thanks to xlbo...

Sub FindLastCell()
Dim lRow As Long, lCol As Integer, mRow As Long, mCol As Integer
lCol = ActiveSheet.UsedRange.Columns.Count
mRow = 0
For i = 1 To lCol
    lRow = Range(Cells(65536, i), Cells(65536, i)).End(xlUp).Row
    If lRow > mRow Then
        mRow = lRow
        mCol = i
    Else
    End If
Next i
LastCell = Range(Cells(mRow, mCol), Cells(mRow, mCol)).Address
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[srogers]

Ok, yes, I see.
Just trying to cover all the bases and it looks like that way of finding the last used cell will do that.

Many thanks-