delete range using VBA
 

Hello
I get a daily report from an outside vendor which I copy and paste into my
report.
On columns AH and AI, I have added formulas to determine if the payment is
late and the amount due
I want to delete the range starting in row 5 to column AG till the last row
that has information. I do not want to delete the formulas on column AH
and AI so they will calculate. I tried
Sub Clear141()
'
' Clear141 Macro

'
Range("A5:AG3424" & Count).Select
Selection.ClearContents
End Sub
This seems to work but is it the best way to do it.
thanks

Answer: delete range using VBA
 

Hi there!

Yes, your code will work to clear the contents of the specified range. However, there are a few things you can do to improve it.

Firstly, you can use the "End" property to find the last row with data in column A, instead of hardcoding the row number. Here's an example:

1. lastRow = Cells(Rows.Count, "A").End(xlUp).Row
2. Range("A5:AG" & lastRow).ClearContents

This will find the last row with data in column A and use that to determine the range to clear.

Secondly, if you only want to clear the contents of the cells and not any formatting or comments, you can use the "Clear" method instead of "ClearContents". Here's the updated code:

1. lastRow = Cells(Rows.Count, "A").End(xlUp).Row
2. Range("A5:AG" & lastRow).Clear

delete range using VBA
 

Hi,

As a general rule there is rarely; if ever, any need to select a range to do
something and not necessary in this case. Try this

Sub Clear141()
Dim LastRow as Long
lastrow = Cells(Cells.Rows.Count, "AG").End(xlUp).Row
Range("A5:AG" & lastrow).ClearContents
End Sub

Mike

"Wanna Learn" wrote:

Hello
I get a daily report from an outside vendor which I copy and paste into my
report.
On columns AH and AI, I have added formulas to determine if the payment is
late and the amount due
I want to delete the range starting in row 5 to column AG till the last row
that has information. I do not want to delete the formulas on column AH
and AI so they will calculate. I tried
Sub Clear141()
'
' Clear141 Macro

'
Range("A5:AG3424" & Count).Select
Selection.ClearContents
End Sub
This seems to work but is it the best way to do it.
thanks