Clear contents of Columns A through H from first row where Col G =

Having tried for many hours without a solution, I am asking for help to please clear the contents of Columns A through H from first row where Col G = - to row 50000. I have tried many approaches without success. Users currently have instructions to do this manually, but I sure wish it could be automated by adding it to the code below. Deleting the rows is no good because it upsets array formulas elsewhere that use this data.

Sub CopyPasteToPrYrData()
'
' CopyPasteToPrYrData Macro
'
' Keyboard Shortcut: Ctrl Shift C
'
    Sheets("Barrel List by Producer").Range("AD3:AK30000").Copy
    Sheets("Prior Years Data").Range("A1:H29998").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Sheet1.Activate
    Range("A1:B29998").Select
        With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    
End With

    Range("A1:H29998").Sort Key1:=Range("G1"), Order1:=xlDescending, Header:=xlNo
    
    
End Sub

I tried creating a concatenated range formula in Excel from calculated Find values, code to copy the first row with only a space in it and copying that down, building a range formula in VBA instead of Excel, and various iterations of those until I gave up on my ability to solve the problem.

CodePudding user response：

you can use AutoFilter() to filter negative values and clear them

here's a possible code, where I also refactored your existing one to add more consistency

Sheets("Barrel List by Producer").Range("AD3:AK17").Copy
With Sheets("Prior Years Data")
    .Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    With .Range("A1").CurrentRegion
        .NumberFormat = "General"
        .Value = .Value
        
        .Sort Key1:=.Range("G1"), Order1:=xlDescending, Header:=xlNo
        
        .AutoFilter field:=7, Criteria1:="<0"
            With .Resize(.Rows.Count - 1).Offset(1)
                If CBool(Application.Subtotal(103, .Columns(1))) Then
                    .SpecialCells(xlCellTypeVisible).ClearContents
                End If
            End With
    End With
    .AutoFilterMode = False
End With

CodePudding user response：

Sub CopyPasteToPrYrData()
'
' CopyPasteToPrYrData Macro
'
' Keyboard Shortcut: Ctrl Shift C
'
    Sheets("Barrel List by Producer").Range("AD3:AK30000").Copy
    Sheets("Prior Years Data").Range("A1:H29998").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Sheet1.Activate
    Range("A1:B29998").Select
        With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    
End With

    Range("A1:H29998").Sort Key1:=Range("G1"), Order1:=xlDescending, Header:=xlNo
    
    Columns("G:G").Select
    Selection.Find(What:="-", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.EntireRow.Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    
End Sub