Clear Contents & Delete rows

G

Gtasios4

Board Regular
Joined
Apr 21, 2022
Messages
80
Office Version
  1. 2021
Platform
  1. Windows
Dear All,

In the below excel table I have a macro for clearing the contents of specific cells & ranges. However, I want to modify it a bit and apart from clearing the contents also to delete any inserted row above the "total bar" and keep rows 1:5.

Any help would be much appreciated.

1652358122346.png

VBA Code: 
Sub ClearAll()
Range("B2", "K2").ClearContents
Range("B4", "K4").ClearContents
Range("B5", "K5").ClearContents
Range("B6", "K6").ClearContents
Range("B7", "K7").ClearContents
Range("B8", "K8").ClearContents
Range("B9", "K9").ClearContents
Range("B10", "K10").ClearContents
Range("B11", "K11").ClearContents
Range("B12", "K12").ClearContents
Range("B13", "K13").ClearContents
Range("B14", "K14").ClearContents
Range("B15", "K15").ClearContents
Range("B16", "K16").ClearContents
Range("B17", "K17").ClearContents
Range("B18", "K18").ClearContents
Range("B19", "K19").ClearContents
Range("B20", "K20").ClearContents
Range("B21", "K21").ClearContents
Range("B22", "K22").ClearContents
Range("B23", "K23").ClearContents
Range("B24", "K24").ClearContents
Range("B25", "K25").ClearContents
Range("M4", "M30").ClearContents
End Sub
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
How about this. If you are going to delete the order line items, do you really have to clear them first... Please test on a backup copy of your work as this code will delete data....

VBA Code: 
Sub ClearAll()
    Dim x As Long
    Dim rng As Range
    Range("B2", "K2").ClearContents
    Set rng = Range("N5:N" & Cells(Rows.Count, 14).End(xlUp).Row).Find(what:="TOTAL:")
    If rng Is Nothing Then Exit Sub
    x = rng.Row
    If x = 5 Then Exit Sub
    Rows("5:" & x - 1).EntireRow.Delete
End Sub
 
Upvote 0
igold said:
How about this. If you are going to delete the order line items, do you really have to clear them first... Please test on a backup copy of your work as this code will delete data....

VBA Code: 
Sub ClearAll()
    Dim x As Long
    Dim rng As Range
    Range("B2", "K2").ClearContents
    Set rng = Range("N5:N" & Cells(Rows.Count, 14).End(xlUp).Row).Find(what:="TOTAL:")
    If rng Is Nothing Then Exit Sub
    x = rng.Row
    If x = 5 Then Exit Sub
    Rows("5:" & x - 1).EntireRow.Delete
End Sub
Click to expand...
Dear igold,

Thank you so much for your kind help & your code. It works perfectly fine, I've modified it as you can see below:

VBA Code: 
Sub ClearAll()
    Dim x As Long
    Dim rng As Range
Range("B2", "K2").ClearContents
Range("B4", "K4").ClearContents
Range("B5", "K5").ClearContents
Range("B6", "K6").ClearContents
Range("B7", "K7").ClearContents
Range("B8", "K8").ClearContents
Range("B9", "K9").ClearContents
Range("B10", "K10").ClearContents
Range("B11", "K11").ClearContents
Range("B12", "K12").ClearContents
Range("B13", "K13").ClearContents
Range("B14", "K14").ClearContents
Range("B15", "K15").ClearContents
Range("B16", "K16").ClearContents
Range("B17", "K17").ClearContents
Range("B18", "K18").ClearContents
Range("B19", "K19").ClearContents
Range("B20", "K20").ClearContents
Range("B21", "K21").ClearContents
Range("B22", "K22").ClearContents
Range("B23", "K23").ClearContents
Range("B24", "K24").ClearContents
Range("B25", "K25").ClearContents
Range("M4", "M30").ClearContents
    Set rng = Range("N5:N" & Cells(Rows.Count, 14).End(xlUp).Row).Find(what:="TOTAL:")
    If rng Is Nothing Then Exit Sub
    x = rng.Row
    If x = 5 Then Exit Sub
    Rows("5:" & x - 2).EntireRow.Delete
End Sub

I just need to keep row 1 to row 5 above "Total" bar of course, therefore I changed Rows("5:" & x - 2).EntireRow.Delete , however when I press the button when the code is attached for a second time then row 4 & 5 are deleted. How can I avoid that and keep them intact? And lastly, can I add a msg box "Are you sure for that" Yes/No?

1652429503617.png
 
Upvote 0
Editing post...
 
Last edited:
Upvote 0
I had made a mistake and would not have been able to repost before I got locked out for editing.
Ok, try this. I just adjusted the code you posted, see if this does what you want...

VBA Code: 
Sub ClearAll()
    Dim x As Long, resp As String
    Dim rng As Range
    resp = MsgBox("Are You Sure You Want To Clear and Delete Rows", vbYesNo, "Clear & Delete")
    If resp = vbNo Then Exit Sub
    Range("B2", "K2").ClearContents
    Range("B4", "K4").ClearContents
    Range("B5", "K5").ClearContents
    Range("B6", "K6").ClearContents
    Range("B7", "K7").ClearContents
    Range("B8", "K8").ClearContents
    Range("B9", "K9").ClearContents
    Range("B10", "K10").ClearContents
    Range("B11", "K11").ClearContents
    Range("B12", "K12").ClearContents
    Range("B13", "K13").ClearContents
    Range("B14", "K14").ClearContents
    Range("B15", "K15").ClearContents
    Range("B16", "K16").ClearContents
    Range("B17", "K17").ClearContents
    Range("B18", "K18").ClearContents
    Range("B19", "K19").ClearContents
    Range("B20", "K20").ClearContents
    Range("B21", "K21").ClearContents
    Range("B22", "K22").ClearContents
    Range("B23", "K23").ClearContents
    Range("B24", "K24").ClearContents
    Range("B25", "K25").ClearContents
    Range("M4", "M30").ClearContents
    Set rng = Range("N5:N" & Cells(Rows.Count, 14).End(xlUp).Row).Find(what:="TOTAL:")
    If rng Is Nothing Then Exit Sub
    x = rng.Row
    If x = 6 Then Exit Sub
    Rows("5:" & x - 2).EntireRow.Delete
End Sub
 
Upvote 0
Alternatively your post could be written like this...

VBA Code: 
Sub ClearAll()
    Dim x As Long, resp As String
    Dim rng As Range
    resp = MsgBox("Are You Sure You Want To Clear and Delete Rows", vbYesNo, "Clear & Delete")
    If resp = vbNo Then Exit Sub
    Range("B2", "K2").ClearContents
    Range("B4", "K25").ClearContents
    Range("M4", "M30").ClearContents
    Set rng = Range("N5:N" & Cells(Rows.Count, 14).End(xlUp).Row).Find(what:="TOTAL:")
    If rng Is Nothing Then Exit Sub
    x = rng.Row
    If x = 6 Then Exit Sub
    Rows("5:" & x - 2).EntireRow.Delete
End Sub
 
Upvote 0
Solution
igold said:
Alternatively your post could be written like this...

VBA Code: 
Sub ClearAll()
    Dim x As Long, resp As String
    Dim rng As Range
    resp = MsgBox("Are You Sure You Want To Clear and Delete Rows", vbYesNo, "Clear & Delete")
    If resp = vbNo Then Exit Sub
    Range("B2", "K2").ClearContents
    Range("B4", "K25").ClearContents
    Range("M4", "M30").ClearContents
    Set rng = Range("N5:N" & Cells(Rows.Count, 14).End(xlUp).Row).Find(what:="TOTAL:")
    If rng Is Nothing Then Exit Sub
    x = rng.Row
    If x = 6 Then Exit Sub
    Rows("5:" & x - 2).EntireRow.Delete
End Sub
Click to expand...
Unbelievable! Works perfect! I am so grateful for your help :)
 
Upvote 0
You're welcome, I was happy to help. Thanks for your feedback!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Clear content in multiple ranges on 14 different worksheets.
Replies
7
Views
587
MAP77
M
N
  • Question
Count - specific consecutive order
Replies
6
Views
582
Peter_SSs
Peter_SSs
J
  • Solved
conditional format to change color
2
Replies
10
Views
287
SanjayGMusafir
SanjayGMusafir
T
  • Solved
VBA - Make same formulas into a single one
Replies
5
Views
290
Michael M
Michael M
B
  • Question
Color Filtered Data
Replies
5
Views
433
gnaga
G

Forum statistics

Threads
1,215,836
Messages
6,127,175
Members
449,368
Latest member
JayHo

Share this page

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
Back
Top