selecting the last non blank row.

R

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
 
P

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
 
K

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
 
F

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
 
K

kkknie

Change:

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

To:

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