Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

ClearContents in VBA


(!)

jillianbk's Avatar
jillianbk jillianbk is offline
Junior Member with 18 posts.
THREAD STARTER
 
Join Date: Jul 2003
28-Jul-2003, 08:37 AM #1
Post ClearContents in VBA
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?
khaki's Avatar
Senior Member with 2,431 posts.
 
Join Date: Jul 2003
28-Jul-2003, 09:26 AM #2
hi Jillian...

it's tough to de-bug without seeing your code...
but here is a basic (very ) 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
jillianbk's Avatar
jillianbk jillianbk is offline
Junior Member with 18 posts.
THREAD STARTER
 
Join Date: Jul 2003
28-Jul-2003, 10:24 AM #3
thanks. i'll try it and let you know what happens.
jillianbk's Avatar
jillianbk jillianbk is offline
Junior Member with 18 posts.
THREAD STARTER
 
Join Date: Jul 2003
29-Jul-2003, 07:25 AM #4
It seeems to be working! Thanks again.
jillianbk's Avatar
jillianbk jillianbk is offline
Junior Member with 18 posts.
THREAD STARTER
 
Join Date: Jul 2003
31-Jul-2003, 10:32 AM #5
Clear Cells
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() [B]
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑