Excel VBA Help
 

Hi I am trying to find the last used cell in column A - copy it, then find
the last blank cell in column A and paste it.

Tried this - but no joy.

'find last cell with data in Column A and copy cell
LastRowColA = Range("A65536").End(xlUp).Row
Selection.Copy


' find first empty cell in column A - then paste in cell contents of above
iRow = ws.Cells(Rows.Count, 1) _
..End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste

ANY IDEAS ANYONE?

Many thanks

Paul T

Excel VBA Help
 

skrev i en meddelelse
...
Hi I am trying to find the last used cell in column A - copy it, then
find the last blank cell in column A and paste it.

Tried this - but no joy.

'find last cell with data in Column A and copy cell
LastRowColA = Range("A65536").End(xlUp).Row
Selection.Copy


' find first empty cell in column A - then paste in cell contents of above
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste

ANY IDEAS ANYONE?

Many thanks

Paul T

Hi Paul

As first empty cell in column A will be one cell below last used cell, you
can do it like this:

'find last cell with data in Column A and copy cell
Dim LastRowColA As Range
Set LastRowColA = Range("A65536").End(xlUp)
LastRowColA.Copy


' find first empty cell in column A - then paste in cell contents of above
LastRowColA.Offset(1, 0).Select
ActiveCell.PasteSpecial (xlPasteValues)

Regards,

Per

Excel VBA Help
 

Maybe this

Range("A65536").End(xlUp).Copy
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial

Mike

" wrote:

Hi I am trying to find the last used cell in column A - copy it, then find
the last blank cell in column A and paste it.

Tried this - but no joy.

'find last cell with data in Column A and copy cell
LastRowColA = Range("A65536").End(xlUp).Row
Selection.Copy


' find first empty cell in column A - then paste in cell contents of above
iRow = ws.Cells(Rows.Count, 1) _
..End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste

ANY IDEAS ANYONE?

Many thanks

Paul T

Excel VBA Help
 

Here's one way:

Sub test()

With ActiveSheet.Range("A65536")
.End(xlUp).Copy .EntireColumn.SpecialCells(xlCellTypeBlanks)(1)
End With

End Sub


--
Hope that helps.

Vergel Adriano


" wrote:

Hi I am trying to find the last used cell in column A - copy it, then find
the last blank cell in column A and paste it.

Tried this - but no joy.

'find last cell with data in Column A and copy cell
LastRowColA = Range("A65536").End(xlUp).Row
Selection.Copy


' find first empty cell in column A - then paste in cell contents of above
iRow = ws.Cells(Rows.Count, 1) _
..End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste

ANY IDEAS ANYONE?

Many thanks

Paul T

Excel VBA Help
 

Many thanks Per & Mike

Both solutions worked.

Many thanks.


wrote in message
...
Hi I am trying to find the last used cell in column A - copy it, then
find the last blank cell in column A and paste it.

Tried this - but no joy.

'find last cell with data in Column A and copy cell
LastRowColA = Range("A65536").End(xlUp).Row
Selection.Copy


' find first empty cell in column A - then paste in cell contents of above
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste

ANY IDEAS ANYONE?

Many thanks

Paul T