1

Solved. I've been looking for code that would allow you to select the range of last column and last row within a spreadsheet. After doing some digging and using some logic, I was able to come up with a solution. First, I defined the last Row using the following: 

LR = Range("A" & rows.count).end(xlup).row and then I used the following to define last column 

LC= wcolNm(colNum)

Public function LC= wcolNm(colNum)

ColNum = Cells(1 & columns.count).end(xltoleft).column
wcolumn = split(cells(1, column).address, "$")(1)
End function

The end state looks as followed:

Range("A" & 1 & ":" & LC & LR).select

Which I inserted after LC. My apologies if this is confusing but I'm new to coding and was looking for this code for a while so I felt like it would be good to post. I'll give more details if needed.

Community
  • 1
  • 1
Malall10
  • 19
  • 1
  • 2

2 Answers2

0

Here is how I typically do it.

Range(Cells(1, Columns.Count), Cells(Rows.Count, 1).End(xlUp)).Select

You don't typically want to use the UsedRange method of selecting cells if your data changes at all.

-1

I believe you can do this in an easier way. Provided you are attempting to select your entire range of data (i.e. A1 to your last row and column), you can use the following method:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
ws.UsedRange.Select

That should select all of your data. This is also assuming you're working with the first worksheet.

bucky310
  • 34
  • 4
  • There is a problem with this method. If he used a cell in the past that's outside of the current range, it will select all the way out to said cell. –  Mar 18 '17 at 00:33