none
clear data in several tabs RRS feed

  • Question

  • Hi

    I have a tab called counterparties. From row 2 in column A I have values which match names of tabs in the same wkb. I want to clear all range data in these tabs. Data are placed in cell D to I from row 22 and around 2000 rows down. There can be spaces between rows. There are data in columns to the left that not should be cleared.

    Best Sverre


    Sverreberre

    Monday, June 18, 2012 11:59 AM

Answers

  • Try this (test it on a copy of the workbook first!)

    Sub ClearData()
        Dim wshS As Worksheet
        Dim wshT As Worksheet
        Dim r As Long
        Dim m As Long
        Set wshS = Worksheets("CounterParties")
        m = wshS.Range("A" & wshS.Rows.Count).End(xlUp).Row
        For r = 2 To m
            Set wshT = Worksheets(wshS.Range("A" & r).Value)
            wshT.Range("D22:I" & wshT.Rows.Count).ClearContents
        Next r
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by sverreberre Monday, June 18, 2012 1:15 PM
    Monday, June 18, 2012 12:48 PM
  • Try changing the lines

    iRowCount = oWS.UsedRange.Rows.Count + 2
    Set oTargetRange = oWS.Range("D" & iRowCount)

    to

    Set oTargetRange = oWS.Range("D" & oWS.Rows.Count).End(xlUp).Offset(2, 0)


    Regards, Hans Vogelaar

    • Marked as answer by sverreberre Wednesday, June 20, 2012 12:14 PM
    Wednesday, June 20, 2012 12:07 PM

All replies

  • Try this (test it on a copy of the workbook first!)

    Sub ClearData()
        Dim wshS As Worksheet
        Dim wshT As Worksheet
        Dim r As Long
        Dim m As Long
        Set wshS = Worksheets("CounterParties")
        m = wshS.Range("A" & wshS.Rows.Count).End(xlUp).Row
        For r = 2 To m
            Set wshT = Worksheets(wshS.Range("A" & r).Value)
            wshT.Range("D22:I" & wshT.Rows.Count).ClearContents
        Next r
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by sverreberre Monday, June 18, 2012 1:15 PM
    Monday, June 18, 2012 12:48 PM
  • Hi

    I noticed something that does not work. I have a code that inserts data into several spreadsheets named after a list. This data is erased with above code. When i run above code it clears the data but the code that inserts data "thinks" that the range where I had the data is used already and therefore it starts inserting from usedrange+2 rows. I dont know if there any possiblitites to edit the code above somehow? If I manually deletes the rows where I have the data it works fine - insertion starts from where I want it. But I have built links which get deleted if I delete the whole row and therefor I cannot delete the row, just erase the cell-range. I attach the code for copy of data to the tabs. I am no VBA-expert but what I think gets wrong is "oTargetRange" in below code. The range needs to be variable as no of rows inserted varies.

    Sub CopyData(ByVal sParty As String, oWS As Worksheet)

    Dim iRowCount As Integer
    Dim oTargetRange As Range
    iRowCount = oWS.UsedRange.Rows.Count + 2
    Set oTargetRange = oWS.Range("D" & iRowCount)

    Dim oFilterRange As Range
    Dim oWSData As Worksheet

    Set oWSData = ThisWorkbook.Worksheets("Data")

    On Error GoTo Err_1:
    With oWSData
        .AutoFilterMode = False
        .Range("A1").AutoFilter Field:=7, Criteria1:=sParty
       
        With .AutoFilter.Range
            If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
                .Offset(1, 1) _
                .Resize(.Rows.Count - 1, .Columns.Count - 1) _
                .SpecialCells(xlCellTypeVisible) _
                .Copy Destination:=oTargetRange
                'oWS.Range("D22:I5000")
               
            End If
        End With
        .AutoFilterMode = False
     End With
     
     Exit Sub
    Err_1:
     Debug.Print Err.Description
     
    End Sub


    Sverreberre

    Wednesday, June 20, 2012 10:48 AM
  • Try changing the lines

    iRowCount = oWS.UsedRange.Rows.Count + 2
    Set oTargetRange = oWS.Range("D" & iRowCount)

    to

    Set oTargetRange = oWS.Range("D" & oWS.Rows.Count).End(xlUp).Offset(2, 0)


    Regards, Hans Vogelaar

    • Marked as answer by sverreberre Wednesday, June 20, 2012 12:14 PM
    Wednesday, June 20, 2012 12:07 PM
  • works!

    tnx,

    Sverre


    Sverreberre

    Wednesday, June 20, 2012 12:14 PM