deleting rows

  • Thread starter Thread starter D
  • Start date Start date
D

D

I have a workbook and I am using an IF formula to move values from one sheet
to another. My problem is that the data is often transferred with sizable
gaps between rows. ( if the if formula doesn't apply on sheet one for
several rows then the data transferred to sheet two will have that many
empty rows in between)
Is there anyway to automatically edit out blank rows?
Thanks
D
 
You could use a macro to copy ONLY the data that meets your criteria. More
info needed.
 
what I have is an IF formula that will copy A1,B1 and C1 from sheet one to
sheet two if sheet one D1 is empty. So if those criteria aren't met, gaps
in rows occur until they are met. is that more helpful?

I actually recorded a macro to filter out blank rows, but I was hoping there
was a way to do it a bit more smoothly. A VB to get rid of blank rows would
be what I need I guess. There are no "views" that I can find that exclude
blank rows.
 
Maybe this will help
Sub RowBeGone()
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

or
Application.ScreenUpdating = False
'For i = 5000 To 2 Step -1
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(i, 1).Value = "" Then Rows(i).Delete
Next
Application.ScreenUpdating = True
 
I will give it a try and let ya know!....thanks


Don Guillett said:
Maybe this will help
Sub RowBeGone()
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

or
Application.ScreenUpdating = False
'For i = 5000 To 2 Step -1
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(i, 1).Value = "" Then Rows(i).Delete
Next
Application.ScreenUpdating = True
Click to expand...
 
hmmm... can't get 'em to work for me, hoped the first one would... seems
like it oughta, the 2nd one I can't even begin to undersatnd :)
but thanks for your efforts!
 
the other problem of course is that the cells aren't displaying data, but
there are "iF" formulas in there, so I guess they aren't actually blank
 
Then try this instead. You are testing for "" in column A

Sub deleteif()
x = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & x)
If c = "" Then c.EntireRow.Delete
Next
End Sub
 
well Don, you have tried above and beyond, but I guess my workbook is just
to "busy".... the poor VB just can't sort it out!
I will just have to put the effort into filtering to "non-blank" rows and
going from there. Thanks so much for all your efforts!!
D
 
well Don, you have tried above and beyond, but I guess my workbook is just
to "busy".... the poor VB just can't sort it out! This is the formula that
shows up in column B ......

=IF(Sheet1!H5="",Sheet1!C5,IF(Sheet1!C5>=1,"")) (I did the nested part to
make the cell not display "false" .)

If I could make the vb based on blank rows based on cells in column b it
might work
I could also not be lazy and just use the filter option!!
Thanks for all your efforts!
D
 
no..I meant A... my mistake.......
Thanks



Don Guillett said:
Didn't you say column A ? If you want to test col B,
change it from a to b.....

--
Don Guillett
SalesAid Software
(e-mail address removed)
part
Click to expand...
 
Back
Top