Find Row Macro Clarification

Question

I've looked at several threads regarding finding the last row/cell on a spreadsheet using a macro and I have a few questions.

First, does the following code only return a row value or an entire cell value ("1" or "A1")?

Dim TOS As Long
TOS = Range("A1").Row

Alternatively, besides the obvious (the fact that they're starting from the bottom and finding the last cell plus 1), is there a difference between these other codes (in how they'll display or pull their data)? I just need the simplest (or best, if you have a good argument) one to return only the row (not the cell).

A.)

Dim TOS As Long
TOS = Range("A65536").End(xlUp).offset(1,0).Row

B.)

Dim TOS As Long
With Application.Workbooks("Test").Sheets(1)
   If WorksheetFunction.CountA(Cells) > 0 Then
       TOS = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If

C.)

Dim TOS As Long
TOS = Range("A65536").End(xlUp).Row + 1

The point behind pulling just the row is that I have two different tables which I have unmerged on the same Worksheet. I need to delete the blank columns between the columns that have data. It won't let me simply delete the blanks cells using the "Remove Blank Cells" code. I'm assuming that's because in the top table, a column may have blank cells, but in the bottom table, the same column may have data. If you can explain how to make the "Remove Blank Cells" code work, that'd probably be easier, except, I need it to only remove blank cells in columns, not rows, since I need to keep the Tables separated.

The second thing I need answered is, if I put a column and a row together, one using a value from a formula (the row) and the other I'm assuming as a string, how would I go about doing that?

ex.)

Range("B" & TOS:"B" & TOE).Select

TOS and TOE are my row values and I need to select all the cells for those rows in the B column.

Answer 1

The help system confirms that Row

Returns the number of the first row of the first area in the range.

so it is just a (Long) number.

If you want to get a reference to the row itself then:

Dim rng As Range
Set rng = Range("A1").EntireRow

---

A) and C) are the same, but they find the last row of column A, whereas B) finds the last used row of the worksheet (not just column A).

Your code should be:

Range("B" & TOS & ":B" & TOE).Select

with the colon ":" inside the quotes (and another ampersand). (There are other ways to write this though.)

To delete a cell, rather than an entire row:

Range("A1").Delete xlShiftUp