Macro Help - Determine data range addresses and last cell

[datadrvn]

In VBA, I’m trying to determine several characteristics of the active sheet in terms of where data begins and ends. Specific pieces I’m looking for are:

Column where first data appears
Column where last data appears
Row where first data appears
Row where last data appears

At this point, I’m not concerned about whether the cell physically contains anything. I know Excel treats empty but formatted cells as being part of the range. I’m OK with that. I’ve only gotten this far by searching literally dozens of posts and trying out various formulas without success.

What I think I’ve concluded is that if I use the following command, I can select the entire data range. Is there a way that I can populate a variable with that selected range, e.g., Rng = “A1:J80”? From there, I can start figuring out how to decode the variable into its components and derive the other pieces .

ActiveSheet.Range([A1], Cells.SpecialCells(xlCellTypeLastCell)).Select

As a follow-on to that, the above formula assumes that the selection range begins in A1. However, that may not be the first cell that data begins, e.g., the data range may actually be C13 :L92. Does anyone know how to achieve this?

Any help or pointers will be appreciated.
Excel 2007

 

[Norie]

For that particular code you could create a reference to it like this.

Set rngData= ActiveSheet.Range([A1], Cells.SpecialCells(xlCellTypeLastCell))

If you want the range you mention which begins in C13 then the code would probably be quite different.

How exactly would you determine what that range was?

 

[Datsmart]

This will give you the CurrentRegion address from your LastCell reference:

addy = Cells.SpecialCells(xlCellTypeLastCell).CurrentRegion.Address
MsgBox addy

 

[datadrvn]

Hi, Norie. Thank you for your quick help. I'm embarrassed to admit that I don't understand what the "Set rngData = " portion is doing. Do I need to declare a variable called "rngData" that would house the range designation being returned?

As to your question about how would I determine where the range starts, that's the follow-on portion of my original post. I'm not going to be fussy. 90% or more of the time the data begins in A1 so any solution will be appreciated. I was just wondering if there was an easy way to determine where the first cell that contains data was, i.e., the upper-left-most cell with data. That would then set the stage for the rest of the command to find the last data cell, i.e., the lowest-right-most cell. My thinking is that would be my new range. Again...that's a nice-to-have. I don't want to be greedy.

John...your solution works, i.e., the end result is "$j$80", which was the address I was looking for. I didn't expect the absolute reference but that works.

You guys are so helpful and great...thank you! Wayne

 

[Norie]

Wayne

Sorry, I wrote that in a bit of a rush.

I was going to add something for the other part but I started doing it but it wasn't making much sense.

Anyway, rngData is a variable, it's a range reference for the data.

You could use it anywhere in the later code when you need to refer to the range.

eg for formatting, copying, clearing etc.

It's not too difficult do work out the range for all the data if the 'start' cell is known and you can work out what other data to include with some sort of 'rule'.

If the range you want is from the start cell down to include all the rows with data and across to include all the columns with data you could try something like this.

Set rngStart = Range("A1") ' creates a reference for the start cell
 
LastRow = Cells(Rows.Count, rngStart.Column) ' find the last row of data in the column the start cell is in
 
LastColumn = Cells(rngStart.Row, Columns.Count) ' find the last column of data in the row the start cell is in
 
Set rngEnd = Cells(LastRow, LastColumn) ' set a reference for the last cell/cell in the bottom right
 
Set rngData = Range(rngStart, rngEnd) ' combine start can end cells to get full range
 
MsgBox rngData.Address ' show the address

Does that make any sense whatsoever?

If you know the address of the start cell just replace A1 with it.

 

[datadrvn]

Hi, Norie. Your code makes perfect sense to me...except that I cannot get it to work. I am not familiar with the way this code is written...still have much to learn but I understand the approach you're taking. I've never used "Set" or variables I didn't have to declare. I copied it as you wrote it directly into a routine with nothing else:

Set rngStart = Range("A1")
Lastrow = Cells(Rows.Count, rngStart.Column)
Lastcolumn = Cells(rngStart.Row, Columns.Count)
Set rngEnd = Cells(Lastrow, Lastcolumn)
Set RngData = Range(rngStart, rngEnd)
MsgBox RngData.Address ' show the address

My data is from a1:J80. When I get to the Set rngEnd line, I get a "Tun-time error 1004, App-defined or Obj-defined error."

As I step through the code, rngStart contains "Line" as that's the contents of cell A1. Lastrow and Lastcolumn remain empty after executing. I don't know if that's what's causing the error at rngEnd. I am so sorry I don't know more and can't explain more. I'm also very appreciative of your patience and willingness to help.

Thx. Wayne

 

[Datsmart]

I get the same error with Norie's code.

This code will give you the address of the congruent range of data based on the last cell of the used range of the worksheet.
It also assigns variables to each of the Start/End Rows and Columns.

Sub RangeTest1()
'set range for congruent cells around last cell of Used Range
Set rng = Cells.SpecialCells(xlCellTypeLastCell).CurrentRegion
'create variables for StartRow, StartColumn, EndRow, EndColumn
    SR = rng.Row    'StartRow
    SC = rng.Column 'StartColumn
    ER = Cells.SpecialCells(xlCellTypeLastCell).Row       'EndRow
    EC = Cells.SpecialCells(xlCellTypeLastCell).Column    'EndColumn
    MsgBox Range(Cells(SR, SC), Cells(ER, EC)).Address 'show the address
'clear memory
    Set rng = Nothing
End Sub

 

[Norie]

Oops, more typos/mistakes on my part.

That's what I get for trying to write posts on your phone and attempting, poorly, to explain things.

There should be Row and Column and the end of the lines of code for LastRow and LastColumn and a couple of other things.

By the way, you should always declare your variables.

If you can put up with it here's the full code, with declarations.

Option Explicit
 
Sub ItBetterWorkThisTime()
Dim rngData As Range
Dim rngStart As Range
Dim rngEnd As Range
Dim LastRow As Long
Dim LastColumn As Long
 
    ' creates a reference for the start cell
    Set rngStart = Range("A1")
    
    ' find the last row of data in the column the start cell is in
    LastRow = Cells(Rows.Count, rngStart.Column).End(xlUp).Row
    
    ' find the last column of data in the row the start cell is in
    LastColumn = Cells(rngStart.Row, Columns.Count).End(xlToLeft).Column
    
    ' set a reference for the last cell/cell in the bottom right
    Set rngEnd = Cells(LastRow, LastColumn)
    
    ' combine start can end cells to get full range
    Set rngData = Range(rngStart, rngEnd)
    
    ' show the address
    MsgBox rngData.Address
 
End Sub

PS The reason rngStart shows line is because that's the value in the cell it references, A1.

 

[datadrvn]

This works perfectly...actually...after experimenting, I've found both John and Norie's solutions work well...I just need more learning. Thank you so much. I think I can meet my immediate needs now. I appreciate both of your efforts.