Clearing contents beyond the "Z" column

[Shivam.Shah]

Hello,

Below is the code I am using for a button on my sheet.

All works well, but it is not clearing the contents of the new row
beyond column "Z". If I make the k value larger (instead of 1 to 25, I
put 1 to 37 as I want it till column AN) then I get an error and
beyond column "Z" no contents are cleared in the new row.

Any help will be much appreciated! Thank you!

********************

Private Sub CommandButton1_Click()

For i = 1 To 10000
If ActiveSheet.Range("C" & i).Value = "BLENDING FACILITIES"
Then
Range("C" & (i + 1) & ":" & "AN" & (i + 1)).Select
Selection.Copy
Selection.Insert Shift:=xlDown

For k = 1 To 25
If ActiveSheet.Range(Chr(65 + k) & (i + 1)).HasFormula
= False Then
ActiveSheet.Range(Chr(65 + k) & (i +
1)).ClearContents
End If
Next
Exit For
End If
Next

End Sub

 

[XLjedi]

Overall, the code strikes me as inefficient.
But without recommending a wholesale revamp using something like a "For Each
MyCell in MyRange" structure...
And not knowing entirely what your end goal is anyway...

The simple answer to your question is:
If you want to use For/Next to step through cells, you should probably use
something like this and just increment the r and c variables.
r = 5
c = 5
ActiveSheet.Cells(r, c).Select

 

[Shivam.Shah]

No worries, I figured it out.

I used the below instead. Thanks!

For k = 1 To 37
If ActiveSheet.Cells(i + 1, k).HasFormula = False Then
ActiveSheet.Cells(i + 1, k).ClearContents