How to delete empty rows between data

[simplyeshu]

Hello all,

I am trying to delete empty rows between data. I am using below command but it is deleting complete data. please help

Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[villy]

Are you trying to delete the entire row or just a specific row in column A?

 

[Manohar M G]

Hi...!!!! try this..

For i = 1 to 10000
Value = ActiveSheet.Cells(i,1).Value
if Value = "" then
ActiveSheet.Rows(i).delete
Next

 

[Weaver]

Hi...!!!! try this..

For i = 1 to 10000
Value = ActiveSheet.Cells(i,1).Value
if Value = "" then
ActiveSheet.Rows(i).delete
Next

That won't work - you'll upset the counter if you go through them 'top down' - you need to start at the bottom and work up. Also, as a general rule, I'd avoid using reserved words as variable names, and you need an 'end if' so long as your if statement goes over more than 1 line.

for i = 10000 to 1 step -1
if isempty(cells(i,1)) then rows(i).delete
next

But there's still a problem with this because you need to start where the data ends, rather at an arbitrary row - if the data ends at row 125, you're going to end up deleting 9,875 rows one at a time before you even hit the data.

for i = cells(rows.count,1).end(xlup).row to 1 step -1
if isempty(cells(i,1)) then rows(i).delete
next

would be better, unless there are rows at the bottom that aren't blank but have nothing in column A.
Trouble is, the OPs original idea should work, unless there are rows where column A is blank and other columns aren't. We need to understand more about the nature of the data itself before answering this one.

 

[simplyeshu]

Thanks weaver