1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

ClearContents in VBA

Discussion in 'Business Applications' started by jillianbk, Jul 28, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. jillianbk

    jillianbk Thread Starter

    Joined:
    Jul 28, 2003
    Messages:
    18
    I am writing a program in Excel using VBA. I have several cells that I wish to clear upon opening the file so that no previously saved data is used in the new calculations. I have written a sub routine to perform the clear contents function for the specified cells. Ultimately the sheets will be hidden, therefore I had to place the sheet_name.Cells(row,column).ClearContents for the
    cells I want to clear. The problem I am having is that the routine seems to work for all of the sheets EXCEPT sheet1. It seems that if I leave off the sheet_name designation while the sheets are unhidden it works fine, however this won't help me ultimately.
    Any suggestions?
     
  2. khaki

    khaki

    Joined:
    Jul 26, 2003
    Messages:
    2,431
    hi Jillian...

    it's tough to de-bug without seeing your code...
    but here is a basic (very :rolleyes: ) way of clearing contents from hidden sheets.

    (just remember that there always needs to be 1 visible sheet. If you try to hide all the sheets.... it will throw an error)

    if this doesn't help you... just post your code :)
    ;) k

    Sub ClearTheDecks()
    For x = 1 To 3
    Worksheets(x).Visible = True
    Worksheets(x).Activate
    Range("A1").ClearContents
    Worksheets(x).Visible = False
    Next x
    End Sub
     
  3. jillianbk

    jillianbk Thread Starter

    Joined:
    Jul 28, 2003
    Messages:
    18
    thanks. i'll try it and let you know what happens.
     
  4. jillianbk

    jillianbk Thread Starter

    Joined:
    Jul 28, 2003
    Messages:
    18
    It seeems to be working! Thanks again.
     
  5. jillianbk

    jillianbk Thread Starter

    Joined:
    Jul 28, 2003
    Messages:
    18
    It was working, but now the sub routine runs, but none of the sheets clear. The code is below. If anyone can help it would be fantastic! I'm so frusterated!

    Sub empty_sheet()

    Dim X As Integer
    Dim Y As Integer

    Dim Range100 As Range
    Dim Range101 As Range
    Dim Range102 As Range
    Dim Range103 As Range
    Dim Range104 As Range
    Dim Range105 As Range
    Dim Range106 As Range
    Dim Range107 As Range
    Dim Range108 As Range
    Dim Range109 As Range
    Dim Range110 As Range
    Dim Range111 As Range
    Dim Range112 As Range
    Dim Range113 As Range
    Dim Range114 As Range
    Dim Range115 As Range
    Dim Range116 As Range
    Dim Range600 As Range
    Dim Range601 As Range
    Dim Range602 As Range
    Dim Range603 As Range
    Dim Range604 As Range
    Dim Range605 As Range
    Dim Range606 As Range


    X = MsgBox("Would you like to clear the data fields and begin a new analysis?", vbYesNo + vbQuestion, Title:="Clear Data Fields")

    Set Range100 = Range("S56:V62")
    Set Range101 = Range("E65:R71")
    Set Range102 = Range("T65:AF71")
    Set Range103 = Range("B87:B109")
    Set Range104 = Range("E86:E111")
    Set Range105 = Range("C3:C12")
    Set Range106 = Range("G6:G7")
    Set Range107 = Range("F9:F12")
    Set Range108 = Range("K3:K13")
    Set Range109 = Range("B21:N23")
    Set Range110 = Range("B32:N34")
    Set Range111 = Range("B45:B50")
    Set Range112 = Range("C44:J50")
    Set Range113 = Range("P4:p5")
    Set Range114 = Range("S4:S9")
    Set Range115 = Range("R11:R14")
    Set Range116 = Range("U11:U14")
    Set Range600 = Range("O40:O43")
    Set Range601 = Range("Q40:Q43")
    Set Range602 = Range("S40:S43")
    Set Range603 = Range("U40:U46")
    Set Range604 = Range("N49:T52")
    Set Range605 = Range("W40:W43")
    Set Range606 = Range("Y40:AD43")




    If X = 6 Then


    Worksheets(1).Visible = True
    Worksheets(1).Activate
    Range105.ClearContents
    Range106.ClearContents
    Range107.ClearContents
    Range108.ClearContents
    Range109.ClearContents
    Range110.ClearContents
    Range111.ClearContents
    Range112.ClearContents
    Range113.ClearContents
    Range114.ClearContents
    Range115.ClearContents
    Range116.ClearContents
    Cells(4, 22).ClearContents
    Worksheets(1).Visible = False


    Worksheets(2).Visible = True
    Worksheets(2).Activate
    Range100.ClearContents
    Range101.ClearContents
    Range102.ClearContents
    Range103.ClearContents
    Range104.ClearContents
    Worksheets(2).Visible = False



    'Clear the data fields linked to the userforms.

    Worksheets(6).Visible = True
    Worksheets(6).Activate
    Range600.ClearContents
    Range601.ClearContents
    Range602.ClearContents
    Range603.ClearContents
    Range604.ClearContents
    Range605.ClearContents
    Range606.ClearContents
    Worksheets(6).Visible = False

    Worksheets(7).Visible = True


    End If

    If X = 7 Then
    Exit Sub
    End If

    End SubSub empty_sheet()
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/150574