Finding Last Column in a given Row

  • Thread starter Thread starter Lance Hoffmeyer
  • Start date Start date
L

Lance Hoffmeyer

Hello,

How do I find the last nonempty column in a given row (say row 2)?

Lance
 
Hi, this function will give you the last non-empty cell in the last
column, just change the variable myRow to the row value as you need.

Function LastCell(Optional ws As Worksheet) As Range
Dim myRow&, myCol&, Rng As Range
If ws Is Nothing Then Set ws = ActiveSheet
Set Rng = ws.Cells
Set LastCell = Rng(1)
myRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
myCol = ActiveSheet.Cells(myRow, Columns.Count) _
..End(xlToLeft).Column
On Error Resume Next
Set LastCell = Intersect(Rows(myRow), Columns(myCol))
End Function

HTH--Lonnie M.
 
oops, this will give you the last non-empty cell in the last ROW.
 
Consider:
lastCell = Activesheet.Cells(2,Columns.Count).End(xltoLeft)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Paste in column B using Last row of either column B, C or D. 10
Help needed 0
Loop relative rows 1
Finding last appearance of given value 6
Setting range to last occupied cell 3
Column number of last column that returns value ignore formulas 8
Getting Last Non-Blank Row Number With VBA 2
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0

Back
Top