Loop and delete

S

staeri

I have the following code to loop through the cells in AZ:

Dim myRange As Range
Dim myCell As Range

With ActiveSheet
Set myRange = Range("AZ14:AZ33")
End With

For Each myCell In myRange.Cells
If myCell.Value = lstObj.Text Then
myCell.Value = ""

'Here I want to delete the values in cell J to BB!!!
End If
Next myCell

Now it deletes the values in myCell but I want to change this to delete
the values in the cells J to BB. How can this be done?

I'm very grateful for fast response.

Regards,

S
 
B

Bernie Deitrick

S,

Cells(myCell.Row,"J").Resize(1,19).ClearContents

Or

Range(Cells(myCell.Row,"J"), Cells(myCell.Row,"BB")).ClearContents

HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

Oops, I counted to AB, not BB:

Cells(myCell.Row,"J").Resize(1,45).ClearContents

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code to delete names works in one WB, but not another WB? 2
Create Sheets from a Range and Copy Data on copy Data on newlycreated Sheets with the Sheet Names 2
shorten sheet name 4
Automatically Adding a Worksheet 14
Advance in a cell range using VBA 3
Prompt For Replace in Selection And More... 0
trying to create sheets and tab names from first colum of data 2
Create tabs against main list, now I need errors 3

Top