Macro VBA - ClearContents problem

[guscrouchend]

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 sometimes 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

 

[VoG]

Try

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

 

[guscrouchend]

Thank you so much. Have a lovely Sunday.

best wishes from central London UK

 

[GTO]

I should think Peter's would work quite well; just a possible alternative:

    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

 

[VoG]

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]

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