Combine row and columns as variables to cell address

mikemowry

New Member
Joined
Dec 28, 2016
Messages
8
Hi, brand new to the forum. Not a newbie, but somewhat experienced with simpler code.

Here's my problem. I want to use the last cell with data as part of the range in an ActiveSheet.Sort.SortFields.Add Key:=Range( ??? statement.

I got this far and stored row and column to variables, but can't get a cell address to place in the sort statement.

Dim lastRow As Long
Dim lastColumn As Integer
Dim lastCell As Range

If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
lastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

'Search for any entry, by searching backwards by Columns.
lastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

MsgBox Cells(lastRow, lastColumn).Address

Set lastCell = Cells(lastColumn, lastRow)

The message box displays $U$711905 and lastRow is set to 711905 and lastColumn is set to 21, but lastCell = Nothing

Thanks for any help you can offer.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,935
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!
Set lastCell = Cells(lastColumn, lastRow)
Your references are backwards. Row is the first argument, Column is the second.
You did it right in your Message Box!
MsgBox Cells(lastRow, lastColumn).Address
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,935
Office Version
  1. 365
Platform
  1. Windows
Thanks, fixed that, but now lastCell = 0
How exactly are you calling "lastCell"?
Can you post the part of your code where you are trying to use it?

There may be other better ways to find the end of your data.
Is the number of columns pre-determined?
For the rows with data, are there any blanks within the data, or will all cells in the data range have a value?
Is there any column that will ALWAYS have a value for all rows with data?
Is there anything below the end of the data that you do not want included in the sort?
 

mikemowry

New Member
Joined
Dec 28, 2016
Messages
8

ADVERTISEMENT

I imported a file from our SAP system. At this point it contains a couple of blank lines every 55 lines which were page breaks and duplicate rows which are headers. This file will vary in size so I don't want to name a fixed range to sort it, like $A$2:$U$999999.

Maybe I'm taking the wrong approach, but I want to find the last cell and use it to sort the data by column A which is the part number. I'm trying to get to $A$2:lastCell as a range reference I can use in the sort statements.

All columns have data, but there can be blank cells where a part number has no bin location. The number of columns can vary as well.

Once I get the data sorted by part number, I have successfully used a counter loop to delete the entire rows that are blank as well as the duplicated row headers.

Here's my code, I thought the Cells function needed the .Address, but that changes the result back to lastRow = Nothing (from 0)

Dim lastRow As Long
Dim lastColumn As Integer
Dim lastCell As Range

If WorksheetFunction.CountA(Cells) > 0 Then

'Search for any entry, by searching backwards by Rows.
lastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

'Search for any entry, by searching backwards by Columns.
lastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

MsgBox Cells(lastRow, lastColumn).Address

Set lastCell = Cells(lastRow, lastColumn).Address


If there's a better way, I'd very much appreciate the education. I'm usually able to google for solutions, but I'm really stuck on this one.

Thanks in advance.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,935
Office Version
  1. 365
Platform
  1. Windows
Here is your problem:
Code:
[COLOR=#333333]Set lastCell = Cells(lastRow, lastColumn).Address[/COLOR]
You don't use .Address when assigning range variables. .Address returns the address of a range, and hence returns a string.
It should just be:
Code:
[COLOR=#333333]Set lastCell = Cells(lastRow, lastColumn)[/COLOR]
 

mikemowry

New Member
Joined
Dec 28, 2016
Messages
8

ADVERTISEMENT

Sorry, but that returns lastCell = 0

Maybe this helps.

When I Ctrl>End, the last cell is actually $V$711908 because I previously deleted the first three rows and the first column to put the data in cell A1
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,935
Office Version
  1. 365
Platform
  1. Windows
Try adding this line at the end of your code and tell me what it returns:
Code:
MsgBox "The last cell on this sheet is: " & lastCell.Address & vbCrLf & _
        "and the value in this cell is: " & lastCell.Value
 

mikemowry

New Member
Joined
Dec 28, 2016
Messages
8
Here you go.

The last cell on this sheet is: $U$711905 and the value in this cell is: 0

I don't understand why when I Ctrl End I go to $V$711908. I'm familiar with how Excel determines the last cell, i.e., formatting, etc. I'm going to try a utility I have which will reset the cell to $U$711905 which has a zero in it and see if the result is different.
 

mikemowry

New Member
Joined
Dec 28, 2016
Messages
8
I inserted an ActiveWorkbook.Save line at the beginning of my code, which predictably changed the last cell found by hitting Ctrl End to $U$711905

This matches the message box report.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top