none
VBA Clear Cell Contents - From a Worksheet List RRS feed

  • Question

  • Hi friends,

    I have 2 Columns


    CellsToClear   |    WorksheetName
    A1                          Sheet1
    B3                          Sheet3
    C4                          Sheet7

    I have put something together , but there is an error

    Sub ClearCells()
    
    
        Dim ws     As Worksheet
        Dim i      As Integer
        Dim oSheets As Worksheet
        
        
        
        Set ws = Worksheets("test")
        For i = 1 To ws.Cells(ws.Rows.Count, "C").End(xlUp).Row     ' Cells to Clear
            Set oSheets = ws.Range(ws.Cells(i, "D").Value2)         ' Worksheet Names
           
            oSheets.Cells.ClearContents
        
        Next i
        
    End Sub

    can someone please advise?


    Cheers Dan :)

    Monday, September 12, 2016 12:13 PM

Answers

  • Does this do what you want?

    Sub ClearCells()
        Dim ws     As Worksheet
        Dim i      As Integer
        Dim oSheets As Worksheet

        Set ws = Worksheets("test")
        For i = 1 To ws.Cells(ws.Rows.Count, "C").End(xlUp).Row     ' Cells to Clear
            Set oSheets = Worksheets(ws.Cells(i, "D").Value)         ' Worksheet Names
            oSheets.Range(ws.Cells(i, "C").Value).ClearContents
        Next i
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Dan_CS Monday, September 12, 2016 3:58 PM
    Monday, September 12, 2016 3:39 PM

All replies

  • Does this do what you want?

    Sub ClearCells()
        Dim ws     As Worksheet
        Dim i      As Integer
        Dim oSheets As Worksheet

        Set ws = Worksheets("test")
        For i = 1 To ws.Cells(ws.Rows.Count, "C").End(xlUp).Row     ' Cells to Clear
            Set oSheets = Worksheets(ws.Cells(i, "D").Value)         ' Worksheet Names
            oSheets.Range(ws.Cells(i, "C").Value).ClearContents
        Next i
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Dan_CS Monday, September 12, 2016 3:58 PM
    Monday, September 12, 2016 3:39 PM
  • Hi Hans,

    good to see you :)

    I still get confused between the cells and worksheet ranges when the  intelisense does not work - so I start getting more confused on what to place at the end.

    oSheets.Range(ws.Cells(i, "C").Value).ClearContents     << was correct

    Thank you so much

    Works beautifully

    and now i can use this for so many other tasks yay :)

    Have a great day!


    Cheers Dan :)


    • Edited by Dan_CS Monday, September 12, 2016 4:07 PM
    Monday, September 12, 2016 3:58 PM