Code to delete row.

[asyamonique]

Good Day,
Is it possible to delete the row between (A:AZ) of the last entry of worksheet datas with a code?

 

[JackDanIce]

Do you mean if the last row of data is found in xxx you'd like code that deletes range Axxx:AZxxx only? Try:

Sub DelLast()

cells(cells(rows.count, 1).end(xlup).row ,1).Resize(,52).delete Shift:=xlup

End Sub

 

[Fluff]

Beaten2it

 

[JackDanIce]

Play SUBSTITUTE(@Fluff's comment, "en2", " ") by Michael Jackson (which just came on the radio!)

 

[Rick Rothstein]

Do you mean if the last row of data is found in xxx you'd like code that deletes range Axxx:AZxxx only? Try:

Sub DelLast()

cells(cells(rows.count, 1).end(xlup).row ,1).Resize(,52).delete Shift:=xlup

End Sub

You do not need the double Cells call as one would be enough...

Cells(Rows.Count, "A").End(xlUp).Resize(, 52).Delete xlUp

 

[Rick Rothstein]

Do you mean if the last row of data is found in xxx you'd like code that deletes range Axxx:AZxxx only? Try:

Sub DelLast()

cells(cells(rows.count, 1).end(xlup).row ,1).Resize(,52).delete Shift:=xlup

End Sub

You do not need the double Cells call as one would be enough...

Cells(Rows.Count, "A").End(xlUp).Resize(, 52).Delete xlUp

Of course, both of our code lines assume that Column A contains data in its cell on the last row that contains any data; however, if that might not be the case (for example, if the cell in Column A could be blank for the last row with data in it), then one of the two following more general code lines could be used, either this one...

[table="width: 500"]
[tr]
	[td]Columns("A:AZ").Find("*", , xlValues, , xlRows, xlPrevious, , , False).EntireRow.Columns(1).Resize(, 52).Delete xlUp[/td]
[/tr]
[/table]

or this one...

[table="width: 500"]
[tr]
	[td]Intersect(Columns("A:AZ").Find("*", , xlValues, , xlRows, xlPrevious, , , False).EntireRow, Columns("A:AZ")).Delete xlUp[/td]
[/tr]
[/table]

 

[asyamonique]

Columns("A:AZ").Find("*", , xlValues, , xlRows, xlPrevious, , , False).EntireRow.Columns(1).Resize(, 52).Delete xlUp

Rick,
Thanks a lot.

 

[JackDanIce]

@Rick Rothstein

You do not need the double Cells call as one would be enough...

Thank you Rick, I originally had Dim x as Long: x = cells(rows.count, 1).End(xlup).Row : Cells(x, 1) and then decided to get rid of x so text copy and replace created the double Cells!

Currently using a Mac which I do not like for VBA, so (likely with typos and mistakes like the one you spotted!) trying to type code directly in here or a standard text editor, and wasn't paying attention as tried to reduce number of code lines, thanks though!