Last piece of the puzzle!

[Bengo]

Good afternoon,

I have written the following code:

Sub NewPO()

'Cleardown unused PO numbers

Range("e" & Rows.Count).End(xlUp).Offset(1, 0).Select

Selection.EntireRow.ClearContents


'Generate new PO number based on last number in coloumn "i"

Range("P4").Value = "GG" & Range("I" & Rows.Count).End(xlUp).Value + 1 & Range("P2").Value

'Copy New PO into Relevant PO box

Range("J" & Rows.Count).End(xlUp).Offset(1, 0).Value = "GG" & Range("I" & Rows.Count).End(xlUp).Value + 1 & Range("P2").Value


'Copy last number into last cell of column "i"

Range("i" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("I" & Rows.Count).End(xlUp).Value + 1


End Sub


I am having issues with the first section (in Bold). What I am trying to do is find the last populated cell in column E, drop down one cell, and clear the contents in the row. For some reason it doesn't clear any text in the relevant cells in that row?

Can anyone see why?

Many thanks

 

[njimack]

1. You don't need to select cells to perform actions on them (although this wouldn't explain the problem you're facing). Try this:

Dim LastRow As Long
LastRow = Cells(Rows.Count, 5).End(xlUp).Offset(1).Row
Rows(LastRow).ClearContents

 

[Bengo]

Thanks

No that doesn't work either. My last populated row is A15 to L15

I have text written in I16 & J16. When I run this, I'd expect it to find the cell E15, drop to row 16, and clear contents. However it doesn't....

 

[njimack]

Are you sure there's nothing else in column E after row 15? Try running this:

Msgbox "Last row in column E is " & cells(rows.Count,5).end(xlup).row

 

[Bengo]

Are you sure there's nothing else in column E after row 15? Try running this:

Msgbox "Last row in column E is " & cells(rows.Count,5).end(xlup).row

Yes, "Last row in column E is 15".... weird

 

[Bengo]

Sorry I've figured it out! I'm being daft. The remain text in the cells in column I and J are what the rest of my Macro are asking it to input. So effectively the whole row is deleted and the rest is then entered

Can you think of a way of selecting all rows below the last populated cell in column E, not just the single one below?

many thanks

 

[Bengo]

Was thinking something like this, but I can't get it to work...

Dim LastRow As Long
LastRow = Cells(Rows.Count, 5).End(xlUp).Offset(1).Row
Rows(LastRow).Select
Selection.End(xlDown).ClearContents

thanks

 

[Bengo]

Ok this worked, although you could probably simplify this compared to mine:

Dim LastRow As Long
LastRow = Cells(Rows.Count, 5).End(xlUp).Offset(1).Row
Rows(LastRow).Select
Rows(ActiveCell.Row & ":" & Rows.Count).ClearContents

Thanks very much for your help, much appreciated!

 

[Fluff]

You can get rid of the select & do this

lastrow = Cells(Rows.Count, 5).End(xlUp).Offset(1).Row
Rows(lastrow & ":" & Rows.Count).ClearContents

 

[Bengo]

You can get rid of the select & do this

lastrow = Cells(Rows.Count, 5).End(xlUp).Offset(1).Row
Rows(lastrow & ":" & Rows.Count).ClearContents

Great thanks! Will do