Combine row and columns as variables to cell address

[mikemowry]

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.

 

[Joe4]

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

 

[mikemowry]

Thanks, fixed that, but now lastCell = 0

 

[Joe4]

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]

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]

Here is your problem:

[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:

[COLOR=#333333]Set lastCell = Cells(lastRow, lastColumn)[/COLOR]

 

[mikemowry]

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]

Try adding this line at the end of your code and tell me what it returns:

MsgBox "The last cell on this sheet is: " & lastCell.Address & vbCrLf & _
        "and the value in this cell is: " & lastCell.Value

 

[mikemowry]

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]

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