ClearContents_Compile error

[MadhukarG]

Hi Experts -
I want to clear contents specifically from C15 to E column (Data is dynamic, the data always starts from Cell C15 and end data may change based on business volume.
So tried below VBA code, but getting error message as Compile error, Argument not optional.

Private Sub CommandButton1_Click()
Dim Sh As Worksheet, Myrange As Range
Set Sh = ActiveSheet
With Sh
Set Myrange = Union.Range("C15:E")
Myrange.ClearContents
End With
End Sub

Please help me if anything is wrong in the code, the error is getting at code line Set myrange = Union(

Thank you
Maddy

 

[Alex Blakenburg]

You need something like this:
(The assumption is that the last row in Column C is the last row that is relevant to the range C:E)

Private Sub CommandButton1_Click()
    Dim Sh As Worksheet, Myrange As Range
    Dim LastRow As Long
    
    Set Sh = ActiveSheet
    With Sh
        LastRow = .Range("C" & Rows.Count).End(xlUp).Row
        If LastRow < 15 Then Exit Sub       ' No valid data from C15 down
        Set Myrange = .Range("C15:E" & LastRow)
        Myrange.ClearContents
    End With
End Sub

 

[Fluff]

How about

Private Sub CommandButton1_Click()
With ActiveSheet
   Intersect(.UsedRange, .Range("C15:E" & Rows.Count)).ClearContents
End With
End Sub

 

[MadhukarG]

You need something like this:
(The assumption is that the last row in Column C is the last row that is relevant to the range C:E)

Private Sub CommandButton1_Click()
    Dim Sh As Worksheet, Myrange As Range
    Dim LastRow As Long
   
    Set Sh = ActiveSheet
    With Sh
        LastRow = .Range("C" & Rows.Count).End(xlUp).Row
        If LastRow < 15 Then Exit Sub       ' No valid data from C15 down
        Set Myrange = .Range("C15:E" & LastRow)
        Myrange.ClearContents
    End With
End Sub

Great Thank you Alex ! It works cool now .
Another query.
Can I combine two macros in one and run at same time, like first I want to clear the contents from those cells and run another second macro to update those cells with the data which is pulled from different workbooks ( I have coding for the second macro)

Currently I have assigned second macro to Button within the activesheet.

Thank you
Maddy

 

[MadhukarG]

Private Sub CommandButton1_Click() With ActiveSheet Intersect(.UsedRange, .Range("C15:E" & Rows.Count)).ClearContents End With End Sub

This is so crisp code, working great.. Thanks Fluff.

 

[Alex Blakenburg]

I put some error trapping in my version in case the data had already been cleared. You can do something similar with Fluff's code:

With ActiveSheet
   If Not Intersect(.UsedRange, .Range("C15:E" & Rows.Count)) Is Nothing Then
        Intersect(.UsedRange, .Range("C15:E" & Rows.Count)).ClearContents
   End If
End With

Can I combine two macros in one and run at same time, like first I want to clear the contents from those cells and run another second macro to update those cells with the data which is pulled from different workbooks ( I have coding for the second macro)

You can either combine them or after clearing the contents just "Call" the other macro.
eg before "End Sub" have the liine
Call YourSecondMacroName

The word call is actually optional and is personal preference. I like to include it so I can search for the word Call if I need to know where subroutines are being called. I also find it to be clearer.

 

[Fluff]

You can call the other macro like

Private Sub CommandButton1_Click()
With ActiveSheet
   Intersect(.UsedRange, .Range("C15:E" & Rows.Count)).ClearContents
End With
Call Macro1
End Sub

Just change Macro1 to the name of your macro.

 

[MadhukarG]

You can call the other macro like

Private Sub CommandButton1_Click()
With ActiveSheet
   Intersect(.UsedRange, .Range("C15:E" & Rows.Count)).ClearContents
End With
Call Macro1
End Sub

Just change Macro1 to the name of your macro.

Hi Fluff,

I have combined the macro as below, I have two concerns below.
1. originally I assigned the macro to button within the sheet so, that user can click on that button and run the macro but, when I am clicking the button the macro is running again without clearing the data already existed.
2. when I am trying to run the macro by going into VBA code edit mode and hit F5 then, its running correct order (cleardata and run 2nd macro but, that too taking little more time now)

Private Sub CommandButton1_Click()
With ActiveSheet
Intersect(.UsedRange, .Range("C15:E" & Rows.Count)).ClearContents
End With
Call CopyRange
End Sub

Sub CopyRange()
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Dim LastRow As Long
Const strPath As String = "C:\Users\Madhukar.Galpalli\Downloads\PII Inventory\"
ChDir strPath
strExtension = Dir("*.xls*")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPath & strExtension)
With wkbSource
LastRow = .Sheets("Group Inventory Input").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Sheets("Group Inventory Input").Range("C8:E" & LastRow).Copy wkbDest.Sheets("Jun HFM Entry").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
.Close savechanges:=False

End With

strExtension = Dir

Loop
Application.ScreenUpdating = True

End Sub

 

[Fluff]

If you comment out the call line, does the data get cleared?

 

[MadhukarG]

If you comment out the call line, does the data get cleared?

Hi Fluff,

Now I have added below code within my existing macro and now its working fine.

With ActiveSheet
Intersect(.UsedRange, .Range("C15:E" & Rows.Count)).ClearContents
End With

Thank you
Maddy