Find Last Row or UsedRange? 2

[VictoriaLJones]

Hi,

I have writen some code in order to copy down the previous cells contents, from a selected row, however I need it to stop when it finds the last row. At the moment it just keeps going and going and...... etc!!!

I know there is a find last row and Used Range property, but I am unsure as to how to incorporate them into my code.

Any help gratefully received!! I have copied the code below.

Thanks
Victoria

Dim oCell as Range

For Each oCell In Selection.Cells
If IsEmpty(oCell) Then
oCell.Offset(-1, 0).Activate
ActiveCell.Copy
oCell.Offset(0, 0).PasteSpecial (xlPasteAll)
Else:
End If
Next

 

[RamziSaab]

to get the rows:

r =thisworkbook.worksheets("Name of Sheet&quot.usedrange.rows.count

to get columns:

c = thisworkbook.worksheets("Name of Sheet&quot.usedrange.columns.count

 

[VictoriaLJones]

Sorry - being a bit dim this morning.... how do I use this code? I want it to select only the cells in column A....

 

[Bowers74]

Hi Victoria,

This updated version of your code should do the trick.

Dim oCell as Range
Dim LastRow as Double
LastRow = Range("A65536").End(xlUp).Row
For Each oCell In Selection.Cells
If IsEmpty(oCell) And oCell.Row <= LastRow Then
    oCell.Offset(-1, 0).Activate
    ActiveCell.Copy
    oCell.Offset(0, 0).PasteSpecial (xlPasteAll)
End If
Next

I hope this helps.

Good Luck!



If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

have a look at the fAQs section - there are at least 2 that deal with different ways of getting the last used row / cell etc

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244