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

Instead of

For k = 1 To 25
If ActiveSheet.Range(Chr(65 + k) & (i + 1)).HasFormula

For k = 1 to 37
If ActiveSheet.Cells(i+1,k).HasFormula


The Cells member can accept either a letter or number as a column
specifier.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Mon, 15 Feb 2010 10:41:39 -0800 (PST), "Shivam.Shah"
wrote:

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

Thanks a lot! Works perfect!

On Feb 15, 3:13*pm, Chip Pearson wrote:
Instead of

* * * * * *For k = 1 To 25
* * * * * * * *If ActiveSheet.Range(Chr(65 + k) & (i + 1)).HasFormula

For k = 1 to 37
* * * * If ActiveSheet.Cells(i+1,k).HasFormula

The Cells member can accept either a letter or number as a column
specifier.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
[email on web site]

On Mon, 15 Feb 2010 10:41:39 -0800 (PST), "Shivam.Shah"

wrote:
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