VBA Code to Move Data up in A Column so there is no blank cells in between

[mrsushi]

Can you help me change this VBA code so that only the SEDOL data in column A is moved up (so its nice and tidy)?

The below method deletes the entire row which is fine to some extent, but it messes up the row numbers , ie if you delete row 3+4+5…..the order of the rows be 1,2,6 etc. this will impact the ability to copy and paste special colum n J

Instead can you advise the code how to move the next available Sedol to move up ie row 19 to row 4, etc



Sub SecurityFormatremove()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & lastrow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[Fluff]

How about

Range("A:A").SpecialCells(xlBlanks).Delete xlUp

 

[mrsushi]

I need it to work from cell A2?

 

[mrsushi]

unfortunately, the code provided didn't work

 

[Fluff]

unfortunately, the code provided didn't work

Care to supply a bit more information? Like in what way didn't it work?

 

[mrsushi]

It looks fine now

 

[Fluff]

Glad to here that & thanks for the feedback.