# code to return last cell in row containing data

#### tbird

##### Board Regular
I have code below that I need to modify.

Code:
``````Sub GroupTranspose()
Dim FromRowA As Long
Dim ToRow As Long
Dim Col As Integer
'----------------------------------
LastLine = Worksheets("Sheet1").UsedRange.Rows.Count
ToRow = 1
For FromRowA = 2 To LastLine
For Col = 3 To 33
Y = Worksheets("Sheet1").Cells(FromRowA, Col).Value
Worksheets("Sheet2").Cells(ToRow + 1, 2).Value = Y
ToRow = ToRow + 1
Next Col

Next FromRowA
End Sub``````

The line that reads "For Col = 3 To 33" needs to be modified to extend only as far as there is data in the individual row. So in english it would read: "For Col = 3 To "last cell in this row containing data".

I know this is fairly simple but I can't find anything on the board that tells me how this is done.

Thanks for the help.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Range("IV5").End(xlToLeft).Column

Will return the column # of the last cell in row 5 that contains a value.

You'll use your variable to denote the "5" within your loop, I imagine.

Hope this helps!

Thanks for the help, but can you provide a little more explanation? This stuff is not intuitive for me. Specifically, what am I supposed to replace with your line of code? Also, what does the "IV" term refer to in the "IV5" part?

Thanks.

Replies
1
Views
211
Replies
3
Views
122
Replies
1
Views
112
Replies
8
Views
222
Replies
0
Views
317

1,203,316
Messages
6,054,702
Members
444,742
Latest member
jmartin9247

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back