Find last cell an another sheet without activating that sheet

[motilulla]

Using Excel 2010

Hello,

I am using below code to find last empty cell in the column “C” having in the active sheet1.

Sub Find_Last_Cell ()
Range("C" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Select
End Sub

Now assume I have active Sheet1…and want to find last empty cell in the column “C” in the sheet2…without activating "Sheet2" how I can do it.

Please suggest a code

Regards,
Moti

 

[rollis13]

All you have to do is reference the Sheet2 and use .Row instead of .Select.

Sub Find_Last_Cell()
    Debug.Print Sheets(2).Range("C" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Row
    'Debug.Print is only for test use to show result in Immediate pane
End Sub

You can also assign the result to a variable to be used elsewhere.

 

[motilulla]

All you have to do is reference the Sheet2 and use .Row instead of .Select.

Sub Find_Last_Cell()
    Debug.Print Sheets(2).Range("C" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Row
    'Debug.Print is only for test use to show result in Immediate pane
End Sub

You can also assign the result to a variable to be used elsewhere.

Hello rollis13, I tried code as below as you suggest I run it from sheet1 it highlight line in yellow and give an error “438” please suggest what I am doing wrong.

Sub Find_Last_Cell_Sheet2()
Sheets(2).Range("C" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Row
End Sub

Regards,
Moti

 

[MARK858]

You need another Sheets(2)

Sub Find_Last_Cell_Sheet2()
Dim x as long
x = Sheets(2).Range("C" & Sheets(2).Cells.Rows.Count).End(xlUp).Offset(1, 0).Row
MsgBox x
End Sub

 

[motilulla]

You need another Sheets(2)

Sub Find_Last_Cell_Sheet2()
Dim x as long
x = Sheets(2).Range("C" & Sheets(2).Cells.Rows.Count).End(xlUp).Offset(1, 0).Row
MsgBox x
End Sub

Hello MARK858, I tried your suggestion I run it from sheet1 it highlight full line in yellow and giving the same error “438” what I am doing wrong?

Regards,
Moti

 

[motilulla]

Hello MARK858, I tried your suggestion I run it from sheet1 it highlight full line in yellow and giving the same error “438” what I am doing wrong?

Regards,
Moti

Hello MARK858, Sorry I did not copy your code fully just pick the line and put it in to old sub, but coping your complete code give me massage 17…it is perfect in sheet2 row 17 is last empty but I want to need select it as it select in sheet1..is it possible?

Regards,
Moti

 

[My Aswer Is This]

Hello MARK858, Sorry I did not copy your code fully just pick the line and put it in to old sub, but coping your complete code give me massage 17…it is perfect in sheet2 row 17 is last empty but I want to need select it as it select in sheet1..is it possible?

Regards,
Moti

You cannot select a cell in another sheet without activating the sheet.
Why do you need to select the cell?

This here will take you to that cell, but it activates the sheet.
A lot of posters here think you need to do a lot of things by fist selecting the cell but that is not the case.

Sub RectangleDiagonalCornersSnipped2_Click()
Application.Goto Sheets(2).Range("C" & Cells.Rows.Count).End(xlUp).Offset(1, 0)
End Sub

 

[motilulla]

You cannot select a cell in another sheet without activating the sheet.
Why do you need to select the cell?

This here will take you to that cell, but it activates the sheet.
A lot of posters here think you need to do a lot of things by fist selecting the cell but that is not the case.

Sub RectangleDiagonalCornersSnipped2_Click()
Application.Goto Sheets(2).Range("C" & Cells.Rows.Count).End(xlUp).Offset(1, 0)
End Sub

Hello My Aswer Is This, thank you for the information I have 25 sheets and each sheet has button to find last cell in the column “C” which do the job perfectly but what happen when I work within different sheets after I noticed I left them in different cells in different columns so I thought if it is possible to organized put all sheets from master sheet with last empty cell in column “C” will be better.. Yes your code worked it select sheet2 and find the last cell selected

I was doing it following way putting in to code “Sheets("Sheet2").Select” in this way I need to put all sheets name to select them.

Sub Find_Last_Cell_Sheet2()
Sheets("Sheet2").Select
Range("C" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Select
Sheets("Sheet1").Select
End Sub

Thanks to everyone for yours help

Regards,
Moti

 

[My Aswer Is This]

So, do you need more help?

Are you saying you want a button on a master sheet that when pressed will take you to the last cell with data in column C?

If not I'm not sure what you want.

Your subject title says:
Find last cell on another sheet without activating that sheet
And do what??
Do you want to enter some data there or what?

 

[bebo021999]

I guess that before running the code, the cursor is randomly positioned on each sheet, possibly anywhere.
You want that when you navigate to a sheet, the cursor is always set to the last row of column C (to facilitate data entry?).
If so, the following code will set the cursor to the desired position:

Option Explicit
Sub Find_Last_Cell()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Sheets ' loop every sheets
    ws.Activate ' then activate it
    ws.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Select ' select last cell
Next
Application.ScreenUpdating = True
Sheets("Sheet1").Activate ' back to first sheet
End Sub