Loop and delete
 

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

Loop and delete
 

Hi,

ActiveCell.Offset(0, -42).Range("A1:AS1").ClearContents

HTH
Carim

Loop and delete
 

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


wrote in message ups.com...
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

Loop and delete
 

Oops, I counted to AB, not BB:

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

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


wrote in message ups.com...
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

Loop and delete
 

Thank's a lot Bernie! Your solution is great.

Regards,

S

Bernie Deitrick skrev:

Oops, I counted to AB, not BB:

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

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


wrote in message ups.com...
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