selecting the last non blank row.

[reefguy]

I need to create a macro to select the last non blank row in a workshee
and then convert it into it's values ( the row has formulas on it, bu
i will need the values only when the macro runs). I hope this make
sense.

Thanks for your help

 

[pikus]

To find the last USED row use this:

lastUsedRow = Worksheets("Sheet1").UsedRange.Row
Worksheets("Sheet1").UsedRange.Rows.Count - 1

To find the first EMPTY row after the last used row omit the "- 1" lik
so:

firstEmptyRow = Worksheets("Sheet1").UsedRange.Row
Worksheets("Sheet1").UsedRange.Rows.Count

The same works with Columns:

lastUsedCol = Worksheets("Sheet1").UsedRange.Column
Worksheets("Sheet1").UsedRange.Columns.Count - 1

- Piku

 

[kkknie]

Assuming your data was in column A, use:

Range("A65536").End(xlUp).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

 

[reefguy]

Thanks.

Is there a way to copy and paste the whole row instead of only on
column

 

[Frank Kabel]

Hi
try
Range("A65536").End(xlUp).Select
Selection.entirerow.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

 

[kkknie]

Change:

Range("A65536").End(xlUp).Select

To:

Range("A65536").End(xlUp).EntireRow.Select