Delete Cells in Excel with VB - HELP! 4

[gtdown]

I have been trying to delete individual cells containing the value of "z" from my spreadsheet using VB. I have been using the code I have found on this website as a base (it is all for deleting entire rows), but for some reason I can't make the conversion work. Can someone please tell me how to delete cells using VB, without deleting the entire row or sheet. Thank you sooo much to the person who comes through, I am too frustrated to continue.

 

[DaleWatson123321]

Hi gtdown,

I believe this is what you want...

First highlight the range you want this to apply to, and then activate it. Use <Control> &quot;A&quot; if you want to highlight the entire sheet.

Sub DeleteCells_With_Z_Only()
Selection.Replace What:=&quot;z&quot;, Replacement:=&quot;&quot;, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Loomah]

Hi gtdown
Heres a (very slightly) different approach

Sub Del_Z()
Dim c As Range
With Worksheets(1).UsedRange
Set c = .Find(&quot;z&quot;, LookIn:=xlValues)
If c Is Nothing Then
MsgBox &quot;Not found!&quot;
Exit Sub
End If
Do
c.ClearContents
Set c = .FindNext(c)
Loop While Not c Is Nothing
End With
End Sub

I find that using 'Find' for this kind of thing is much quicker if you have a very large data set. If you don't, it doesn't matter!!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[DaleWatson123321]

Loomah,

Thanks for your contribution. Alternative solutions are what makes Tek-Tips a much better forum !!!

I was all set to award you a STAR, but in testing your routine, I came up with the following &quot;REDLINES&quot;...

Is is something I'm doing wrong, or can you double-check the routine for something you might have missed. Thanks.

Sub Del_Z()
Dim c As Range
With Worksheets(1).UsedRange
    Set c = .Find(&quot;z&quot;, LookIn:=xlValues)
    If c Is Nothing Then '<===== REDLINE
        MsgBox &quot;Not found!&quot;
        Exit Sub '<===== REDLINE
    End If '<===== REDLINE
        Do
            c.ClearContents
            Set c = .FindNext(c)
        Loop While Not c Is Nothing '<===== REDLINE
End With
End Sub

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[gtdown]

These work great for clearing the contents, but the cells still remain. I need to delete the cells and shift the others up. Thanks for your help so far, do either of you know how to reach the next step. I have tried substituting delete commands, but I always get this message, which is the same message I got before I solicited your help.......
&quot;Unable to get the FindNext property of the Range Class&quot;. Well, that is not the exact message I was getting before, but all error messages I have gottten seem to focus on the Range class. Let me know if you have an idea, thanks!!!

 

[Loomah]

This is actually a bit rushed so will need full testing. Also it's probably not the best code ever. I've taken the sledgehammer-to-crack-a-nut approach

Sub Del_Zv2()
Dim c As Range
With Worksheets(1).UsedRange
Do
Set c = .Find(&quot;z&quot;, LookIn:=xlValues)
If c Is Nothing Then
Exit Do
End If
c.Delete (xlShiftUp)
Loop
End With
End Sub

Dale,
I'd written a full reply to you then got booted out of tek-tips for some reason. I re tested the code from what you posted back as I've lost the orig already! Couldn't find fault with it. I'm at a loss to explain! Well and truly stumped. Could you try it again after closing xl??(again, the sledgehammer approach!)

Home time
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[gtdown]

Thanks for all your help, I got it to go, here is the winner................

Private Sub DeleteZCells_Click()
Dim c As Range
With Worksheets(&quot;Sheet3&quot.UsedRange
Set c = .Find(&quot;z&quot;, LookIn:=xlValues)
If c Is Nothing Then
MsgBox &quot;Not found!&quot;
Exit Sub
End If
Do
c.Delete
Set c = .Find(&quot;z&quot;, LookIn:=xlValues)
Loop While Not c Is Nothing
End With
End Sub

 

[DaleWatson123321]

Loomah,

First, an update to my problem in copying/pasting your code...

It turned out that when I copied the code from Tek-Tips directly into Excel, the pasted code added &quot;spaces&quot; - but just on those lines that showed as REDLINED.

This was the very first time this has happened to me. And, interestingly, when I just now copied your same code into the same Module, I encountered NO problem. &quot;Go figure&quot;.

Anyway, your last set of code works GREAT, and so as promised, a ===> STAR <===

gtdown,

A ===> STAR <=== to you too for your modified version.

These routines will be great to store in my &quot;library&quot;. Thanks.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca