Macro VBA - ClearContents problem

guscrouchend

Board Regular
Joined
May 11, 2010
Messages
94
Hi,

I'm working with the following:

Sub SendtoInvoice()

With Worksheets("Invoices")

.Range("$A$18:$I" & Cells(Rows.Count, "I").End(xlUp).Row).ClearContents

End With

With Worksheets("Combined Accounts")

.Range("A1:I" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy Worksheets("Invoices").Range("A18")

End With

End Sub

My problems is that the macro :confused:sometimes:confused: clears the contents of all cells in the Invoices worksheet, rather than only the cells from line 18 onwards.

Is there a way to change the clear contents line so that it only ever clears rows 18+?

Thanks

Angus
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Dim LR As Long
With Worksheets("Invoices")
    LR = WorksheetFunction.Max(.Cells(Rows.Count, "I").End(xlUp).Row, 18)
    .Range("$A$18:$I" & LR).ClearContents
End With
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,155
I should think Peter's would work quite well; just a possible alternative:
Rich (BB code):
    With Worksheets("Invoices")
        If .Cells(.Rows.Count, "I").End(xlUp).Row >= 18 Then
            .Range("A18:I" & .Cells(.Rows.Count, "I").End(xlUp).Row).ClearContents
        End If
    End With

Not tested, so in a junk copy of your workbook. I would personally qualfy .Cells as belonging to the sheet, lest another sheet is active and things go KABOOM!. I haven't worked in 2007/2010, but have read implications that unqualified Rows.Count can likewise cause issues. Not sure about that, but the qualification certainly cannot hurt.

@VoG

Hi Peter,

I see that you are now using 2010 at home. Have you by chance ever tested an unqualified Rows.Count at home, on a wb written at work?

Mark
 
Last edited:

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hi Mark

No I haven't had an issue with unqualified Rows.Count.

With the OP's code I thought that it could clear the wrong range if Cells was not qualified and/or column I was filled to less than Row 18.
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,155
With the OP's code I thought that it could clear the wrong range if Cells was not qualified and/or column I was filled to less than Row 18.

Yes sir, I utterly agree

No I haven't had an issue with unqualified Rows.Count.

If you happen to take a wb home (written in 2000 at work) and find this glitch, would you let me know? A description is here at post#1:

http://vbaexpress.com/forum/showthread.php?t=18978

Thank you Peter, and of course, a great day to your and yours,

Mark
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,836
Messages
5,833,904
Members
430,242
Latest member
Bancam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top