clearContents removing wrong content when sheet is changed

[Jnb99]

Hi everyone,

I have a workbook that I am using for estimates and invoices, with code that save as Pdf, create a new sheet as a clone of the estimate you created, and lastly clear contents of the estimate template after everything is saved.

My problem gets in when the sheet is changed, in terms of amount of rows where items are listed. What happened today is the accounts department deleted rows, which moved details into the clearContents area that's not supposed to be cleared. Is there a way apart from a userform, that one can link the area where items are listed with clear contents? I.e. if rows are added or deleted, automatically change clearContents range?

Code below

Private Sub CommandButton1_Click()
 
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("Estimate")
Set WS2 = Worksheets("EDatabase")
' Figure out which row is next row
nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
'Write the important values to register
WS2.Cells(nextrow, 1).Resize(, 4).Value = Array(WS1.Range("F4").Value, WS1.Range("F3").Value _
                                               , WS1.Range("A14").Value, WS1.Range("EstTot").Value)

With Worksheets("Estimate")
  docno = .Range(.Cells(4, 6), .Cells(4, 6))
End With


Call saveAsPdf


With Worksheets("EDatabase")
 .Hyperlinks.Add Anchor:=.Range(.Cells(nextrow, 1), .Cells(nextrow, 1)), Address:="", SubAddress:= _
        docno & "!A1", TextToDisplay:=docno
End With

End Sub
  
Sub saveAsPdf()
Dim saveLocation As String
    Dim rng As Range
    saveLocation = "C:\Users\***\Estimates\" & Range("f4").Value & Range("a14").Value & ".pdf"
    
    Set rng = Worksheets("Estimate").Range("A1:g50")
    rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation
    
    Call saveSheetWithoutFormulas
    
End Sub
Sub saveSheetWithoutFormulas()
    Dim ws As Worksheet
'f4 is document number
    Set wh = Worksheets(ActiveSheet.Name)
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    If wh.Range("f4").Value <> "" Then
    ActiveSheet.Name = wh.Range("f4").Value
    End If
    wh.Activate
With Sheets("Estimate").Range("f4")
     .Value = "E" & (Mid(.Value, 2) + 1)
End With
 Call clearContents
End Sub

Sub clearContents()
Range("a23:d43").clearContents
Range("f23:f43").clearContents
Range("a14").clearContents

End Sub

Thanks in advance

 

[offthelip]

If you know what the contents of any of the rows at the bottom are going to be ( i:e in the rows a44 to d44 or f44), you could search for that text to find it and delete down to that row. You could also do the same for the start row if you know what is gong to be in A22 to d22 or f22. Or any of the the rows that signify the start and end of the "estimate" area

 

[Jnb99]

@offthelip to the rescue again!
Yea that sounds doable if I move some things around to have text directly below the row where the items end.
Will you be able to help me with the code?
Text in question is “Banking details”

 

[offthelip]

try this which does the first range, the others can use the same botrow

Dim Rang As Range
Set Rang = ActiveSheet.UsedRange.Find("Banking Details", LookIn:=xlValues)
botrow = Rang.Row - 1
Range(Cells(23, 1), Cells(botrow, 4)).ClearContents

 

[Jnb99]

I can insert this under the same clearContents sub?

 

[offthelip]

Yes

 

[Jnb99]

Looks like its working fine! How will I add the C, D and F column to clear the same are as the above code does? For example, Items will always start at A23, but the bottom may vary. So C, D and F column has size color and qty values, which must be cleared as well. And lastly A14 has a drop down selector for the clients, which I would like to clear as well?

 

[offthelip]

this will do it all. sorry I thought you would be able to work it out.:

Dim Rang As Range
Set Rang = ActiveSheet.UsedRange.Find("Banking Details", LookIn:=xlValues)
botrow = Rang.Row - 1
Range(Cells(23, 1), Cells(botrow, 4)).ClearContents ' this clears columns 1 to 4 i.e A, B , C and D
Range(Cells(23, 6), Cells(botrow, 6)).ClearContents ' This clears column 6 i.e. F
Range("a14").ClearContents ' this clears A14!!

 

[Jnb99]

this will do it all. sorry I thought you would be able to work it out.:

Dim Rang As Range
Set Rang = ActiveSheet.UsedRange.Find("Banking Details", LookIn:=xlValues)
botrow = Rang.Row - 1
Range(Cells(23, 1), Cells(botrow, 4)).ClearContents ' this clears columns 1 to 4 i.e A, B , C and D
Range(Cells(23, 6), Cells(botrow, 6)).ClearContents ' This clears column 6 i.e. F
Range("a14").ClearContents ' this clears A14!!

lol no.. I am a rookie!

Thank you so much! This workbook can now almost make coffee by itself!