ExcelBanter - Understanding .End(xlUp) (1,1)

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Understanding .End(xlUp) (1,1) (https://www.excelbanter.com/excel-discussion-misc-queries/582-understanding-end-xlup-1-1-a.html)

Understanding .End(xlUp) (1,1)

Using XL 2003 & 97

Apparently, I just do not get it.

I wish to enter "Test" into the first empty cell in Column A.

Tried .Value and .FormulaR1C1 to no avail.

The code does not bomb but "Test" does not show up anywhere on
Worksheet("Sheet2").

Is xlUp(1, 1) kind-of like Offset(1, 1)?

Sub Test()

With Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(1, 1).Value = "Test"
End With

End Sub

TIA Dennis

Answer: Understanding .End(xlUp) (1,1)

Hi Dennis,

I understand your confusion with the

Formula:


		
			
.End(xlUp)

function. Let me explain it to you in detail.

The

Formula:


		
			
.End(xlUp)

function is used to find the last used cell in a column. In your code, you are using it to find the last used cell in column A. The

Formula:


		
			
(1,1)

after the

Formula:


		
			
.End(xlUp)

function is used to move one cell down and one cell to the right of the last used cell in column A.

So, in your code, the

Formula:


		
			
.End(xlUp)(1,1)

refers to the first empty cell in column A. This is where you are trying to enter the value "Test".

However, there is a mistake in your code. You are trying to assign the value "Test" to the

Formula:


		
			
.Value

property of the

Formula:


		
			
.End(xlUp)(1,1)

cell inside the With statement. This is not the correct syntax. You should first select the cell and then assign the value to it.

Here's the corrected code:

Sub Test()
With Sheets("Sheet2")
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = "Test"
End With
End Sub

In this code, we first select the worksheet "Sheet2" using the With statement. Then, we use the

Formula:


		
			
.Cells

property to refer to the last used cell in column A using the

Formula:


		
			
.Rows.Count

and

Formula:


		
			
.End(xlUp)

functions. We then use the

Formula:


		
			
.Offset

function to move one cell down from the last used cell and assign the value "Test" to it.

I hope this helps you understand the

Formula:


		
			
.End(xlUp)

function better.

Dennis

End(xlUp) starts looking at row 65536 and moves up until it finds data.

The .Offset(1,1) goes back down one row and over one column.

..Offset(1,0) would select last empty cell in column A

Sub Test()
With Sheets("Sheet2")
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).Value = "Test"
End With
End Sub

Places the word test into column B in last empty row of Column A

Gord Dibben Excel MVP

On Thu, 25 Nov 2004 16:05:01 -0800, Dennis
wrote:

Using XL 2003 & 97

Apparently, I just do not get it.

I wish to enter "Test" into the first empty cell in Column A.

Tried .Value and .FormulaR1C1 to no avail.

The code does not bomb but "Test" does not show up anywhere on
Worksheet("Sheet2").

Is xlUp(1, 1) kind-of like Offset(1, 1)?

Sub Test()

With Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(1, 1).Value = "Test"
End With

End Sub

TIA Dennis

Thanks for your time and knowledge!

Dennis

"Gord Dibben" wrote:

Dennis

End(xlUp) starts looking at row 65536 and moves up until it finds data.

The .Offset(1,1) goes back down one row and over one column.

..Offset(1,0) would select last empty cell in column A

Sub Test()
With Sheets("Sheet2")
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).Value = "Test"
End With
End Sub

Places the word test into column B in last empty row of Column A

Gord Dibben Excel MVP

On Thu, 25 Nov 2004 16:05:01 -0800, Dennis
wrote:

Using XL 2003 & 97

Apparently, I just do not get it.

I wish to enter "Test" into the first empty cell in Column A.

Tried .Value and .FormulaR1C1 to no avail.

The code does not bomb but "Test" does not show up anywhere on
Worksheet("Sheet2").

Is xlUp(1, 1) kind-of like Offset(1, 1)?

Sub Test()

With Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(1, 1).Value = "Test"
End With

End Sub

TIA Dennis

Dennis wrote:
Using XL 2003 & 97

Apparently, I just do not get it.

I wish to enter "Test" into the first empty cell in Column A.

Tried .Value and .FormulaR1C1 to no avail.

The code does not bomb but "Test" does not show up anywhere on
Worksheet("Sheet2").

Is xlUp(1, 1) kind-of like Offset(1, 1)?

Sub Test()

With Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(1, 1).Value = "Test"
End With

End Sub

TIA Dennis
xlUp(1,1) is the same as xlUp.Item(1,1); omitting "Item" is an
equivalent syntax. xlUp(1,1) is also equivalent to xlUp.Offset(0,0).
Said differently, Offset is 0-based and the Item Properrty is 1-based.

Alan Beban