Thread: Macro to Delete Row and Update Cell Value

I'm a bit confused by the 2nd part of your request. You would like the value of AA1 to be set equal to whatever is in AR1, but that value needs to be 1? The value in both cells of the attached test.xls workbook is the string AAAA.

You would need to tie the following to an ActiveX command button or Form Control.

Sub DeleteAndChange()
'/======================================
'/ Tie to CommandButton_1; Sheet 1 or Form Control
'/ Will delete rows 1:22; AA1 to AR1
'/======================================
Sheets("Sheet1").Range("1:22").EntireRow.Delete

Range("AA1") = Range("AR1")

End Sub

Hi AlvaroSiza,
Thanks for the help.
The 2nd part, I would like the value on those cells to be changed to 1, replacing whatever it is in there.
Is that possible?
I will try the delete code and report back shortly. Thanks again.

Revise previous to the following.

Originally Posted by AlvaroSiza

Sub DeleteAndChange()
'/======================================
'/ Tie to CommandButton_1; Sheet 1 or Form Control
'/ Will delete rows 1:22; AA1 to AR1
'/======================================
Sheets("Sheet1").Range("1:22").EntireRow.Delete

Range("AA1") = 1
Range("AR1") = 1

End Sub

Tried it! It's working except for the part where it changes the value to 1. It only replaces AA1 and AR1 instead of AA1 to AR1.
Do I need to code it individually? AB1=1, AC1=1 etc?

Thanks again sir!

Oh...I see now. Hang on.

Okay sir =)

Sub DeleteAndUpdate()
'/======================================
'/        Function(s):
'/             - Deletes rows 1 to 22
'/             - Sets values of AA1:AR1 = 1
'/======================================
Application.ScreenUpdating = False
     Sheets("Sheet1").Range("1:22").EntireRow.Delete
     Range("AA1:AR1") = 1
Application.ScreenUpdating = True
End Sub

Wonderful! Thank you very much sir!
Just what I wanted. =)