Find a Blank Cell and Delete Partial Row using VBA

[Vinnyc11]

Hello, I'm brand new to VBA programming and I'm having an issue.


I have a Range of data contained within Columns A:G, in which the the last row used varies each use. I want a Macro that will look in column C for a blank cell, once found it will delete the row in which the cell is contained from columns A:G, and then shift those 5 columns up.

 

[shrivallabha]

Welcome to Chandoo.org forums.


Please post what you have tried so far. That way 'we' will learn something together.

 

[imla]

Try this:

[pre]

Sub DelRowUp5Columns()
Dim rg As Range, C As Range, CG As Range

Set C = Intersect(Range("C:C"), ActiveSheet.UsedRange)
For Each rg In C
If rg = "" Then
Set CG = Intersect(rg.EntireRow, Range("C:G"))
CG.Delete Shift:=xlUp
End If
Next rg
End Sub

[/pre]

 

[Debraj]

Hi Vinnyc11


Welcome to the forum..


One more addition..

[pre]

Sub Vinnyc11()
With Intersect(ActiveSheet.UsedRange, Range("A:G"))
.AutoFilter 3, ""
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Delete -4162
.AutoFilter
End With
End Sub

[/pre]

Regards,

Deb


... once again this guy did the same... stopped at only 1 now...

 

[shrivallabha]

imla,


It is not straightforward when you delete rows /cells using For...Each loop. Try to keep some consecutive blank cells in between and you should be able to see what I mean.


Edit: Loops tend to be slower so Deb's method will give results much faster which may not be apparent for small data but if your data runs into thousands then speed would be a factor.

[pre]

Sub DelRowUp5Columns2()
Dim rg As Range, C As Range, CG As Range

Application.ScreenUpdating = False

Set C = Intersect(Range("C:C"), ActiveSheet.UsedRange)
For Each rg In C
If Len(rg) = 0 Then
If CG Is Nothing Then
Set CG = Intersect(rg.EntireRow, Range("A:G"))
Else
Set CG = Union(CG, Intersect(rg.EntireRow, Range("A:G")))
End If
End If
Next rg

If Not CG Is Nothing Then CG.Delete Shift:=xlUp

Application.ScreenUpdating = True

End Sub

[/pre]

 

[imla]

@Shrivallabha,

Thanks, this is why I'm here to learn from the pro!

I'm new VBA coder and I learned from this site a lot.

Yes, I see my error...I will not do it again.

If I wait another day before answring to this question (after viewing codes in chandoo.org forum) my code will look exactly as DelRowUp5Columns2().

But I hope in the future I can code like Deb's method witch I still don't understand why "-4162" ?!!

is it always faster to use built in excel function? is there a resource that I can read for optimizing VBA code for speed?


Thanks again

 

[Debraj]

Hi imla,


Welcome to the Forum as well as VBA world..


In VBA > in Code area.. write "XlShiftUp", and select and press F1..


It will not only gives you help file.. but also the Index Number for XlShiftUp.


Regarding optimizing.. using INDEXing is always faster than writing "XlShiftUp"..


Regards,

Deb

 

[imla]

Thanks for the tip Debraj,


In the future I will use 'index number' for my codes; but for public code I prefer to use 'xlShiftUp' for readability...


Today I learned a lot!!

 

[NARAYANK991]

Hi ,


I think we should stop over-praising the importance of optimizing , especially when the difference is in microseconds !


The reason to use enumerations as they are called is that they are readable ; they can only be used when you use early binding , because when you use late binding , not only is Excel's Intellisense no longer available , but you cannot use enumerations relating to the object which has been used with late binding.


For another advantage of enumerations , see here :


http://www.wiseowl.co.uk/blog/s225/enumerations.htm


If you still wish to use enumerations even with late binding , see this link :


http://www.datapigtechnologies.com/downloads/Excel_Enumerations.txt


Any programmer worth his / her salt will recommend that you use xlShiftUp instead of -4162 ( readability over performance , especially when the advantage of readability is substantial , and the disadvantage of lower performance is insignificant ) , more so if you wish to write code exceeding a couple of lines , and you wish to maintain code for years.


Narayan

 

[shrivallabha]

I agree with Narayan in this. Regarding Early and Late, I try to build my code using early binding as much as possible and while deploying them change them to late binding. But the choice really remains with the coder.


Imla, it is not error per se. It is just programming oversight and since you are learning, there is nothing like trying and making mistakes. I make lots of mistakes myself and learn from them.


But I was actually waiting for OP's reply to see what he had worked out.

 

[SirJB7]

@All

Hi!

I'd personally take my time to search who coded -4162 instead of xlShiftUp and I'll send him/her a backward illuminated wireless keyboard like Logitech G19 (http://www.amazon.com/Logitech-Programmable-Gaming-Keyboard-Display/dp/B001NXDBI6).

Regards!

PS: And while he's typing after having unpacked & installed it since it's a new nice toy, maybe doesn't pay attention to the big display countdown 3...2...1...0, and problem solved.

PS2: Did I mention the quarter pound of C4 attached to the back of the keyboard?

PS3: Yesterday was Echelon, today is Prism, ... and our KeyMaster is in the US, would they let him come back after this or should we post mail him to Guantanamo?