Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro - If , then (Excel 2002)

    Hi,
    I need to have a macro loop through row by row of data in an Excel database... when it loops through each row and the result of column A is the letter S, then go to row R thru Z and clear the contents. If it isn't the letter S, then leave it alone and go to the next line. The code below is what I have so far, IF the letter in column A happened to be in row 66...
    As always... thanks for the help!
    Lana

    Sub deletesubtotalzeros()
    Range("R66:Z66").Select
    Selection.ClearContents
    End Sub

  2. #2
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro - If , then (Excel 2002)

    Hi Hans...something in the tab line of the code is causing an error (see below)... it's showing up in red font.
    Thanks,
    Lana

    tab]Range("R" & lngRow & ":Z" & lngRow).DeleteContents

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro - If , then (Excel 2002)

    Sorry about that, I messed up the indentation. It should be
    <code>
    Range("R" & lngRow & ":Z" & lngRow).DeleteContents
    </code>
    (I have corrected my previous reply too)

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro - If , then (Excel 2002)

    Hi Hans... it's still having a little trouble... I keep getting the 438 error "Object doesn't support this property or method", and it highlights that same line.
    Thanks!
    Lana

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro - If , then (Excel 2002)

    Sorry again, it's been a long day (it's almost 10 PM here). As in your own code, it should have been ClearContents instead of DeleteContents. Here is the complete code again, with a little enhancement:
    <code>
    Sub DeleteSubTotalZeros()
    Dim lngRow As Long
    For lngRow = 1 To Range("A65536").End(xlUp).Row
    If UCase(Range("A" & lngRow)) = "S" Then
    Range("R" & lngRow & ":Z" & lngRow).ClearContents
    End If
    Next lngRow
    End Sub</code>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro - If , then (Excel 2002)

    Added later: see corrected code later in this thread

    Try this (modify as needed):

    Sub DeleteSubTotalZeros()
    Dim lngRow As Long
    For lngRow = 1 To Range("A65536").End(xlUp).Row
    If Range("A" & lngRow) = "S" Then
    Range("R" & lngRow & ":Z" & lngRow).DeleteContents
    End If
    Next lngRow
    End Sub

    lngRow is used as a loop counter - it goes from 1 up to the row number of the last non-blank cell in column A.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro - If , then (Excel 2002)

    Hi Hans,
    That worked great Hans... thanks so much... now I'd like to add to the macro you have below to the "refresh the pivot table" macro I have used for many other projects (you probably wrote that one too)... anyway, this macro performs a refresh of the pivot table when I click on the worksheet tab... I'd like the "clear contents" macro to run when I click on the worksheet tab, and then have the "refresh the pivot table" macro run right after that. I've played around with it for awhile and I keep getting errors... below is my meager attempt... as usual, your verbal explanations are welcomed when you give the right code... this helps me understand what you just did.
    Thanks so much!
    Lana

    Private Sub Worksheet_Activate()
    Dim lngRow As Long
    ActiveSheet.Worksheet ("RP Weekly")
    For lngRow = 1 To Range("A65536").End(xlUp).Row
    If UCase(Range("A" & lngRow)) = "S" Then
    Range("R" & lngRow & ":Z" & lngRow).ClearContents
    Range("AH" & lngRow & ":AT" & lngRow).ClearContents
    End If
    Next lngRow
    ActiveSheet.PivotTables("PivotTable1").RefreshTabl e
    End Sub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro - If , then (Excel 2002)

    Omit the line

    ActiveSheet.Worksheet ("RP Weekly")

    It doesn't make sense.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro - If , then (Excel 2002)

    I would like the macro to automatically run when I click on that particular worksheet. For example, with the refresh pivot table macro below, when I click on the worksheet tab called "RP Weekly", it performs the macro automatically as I have this macro saved on that particular worksheet, not in a module. I guess I see why I don't actually need the worksheet name since I'm saving the macro as part of the worksheet as opposed to a module, however I still can't get it to run when I click on the tab to work on that worksheet. Does this make sense??
    Thanks!
    Lana
    Private Sub Worksheet_Activate()
    ActiveSheet.PivotTables("PivotTable1").RefreshTabl e
    End Sub

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro - If , then (Excel 2002)

    Do you mean that

    Private Sub Worksheet_Activate()
    ActiveSheet.PivotTables("PivotTable1").RefreshTabl e
    End Sub

    doesn't work? In what sense? Do you get an error message (if so, what does it say), or doesn't it do anything?

  11. #11
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro - If , then (Excel 2002)

    Oops Sorry Hans... I didn't explain that very well. I'm not having any problems with the refresh pivot table macro... I'm really just wanting to combine the two macros into one. The refresh pivot table macro runs automatically when I click on the worksheet tab "RP Weekly" because I have it saved as part of that worksheet, as opposed to a module. I'd like the "DeleteSubTotalZeros" macro to run automatically when I click on the "RP Weekly" worksheet tab just LIKE the refresh pivot table macro works. I'm assuming the ActiveSheet function has something to do with it? Hopefully this makes better sense??
    Thanks Hans!
    Lana

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro - If , then (Excel 2002)

    Are the rows to be deleted in the RP Weekly worksheet too?

  13. #13
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro - If , then (Excel 2002)

    Good point... the answer is no... it's in the "data" worksheet.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro - If , then (Excel 2002)

    So, if I understand correctly, when you activate the RP Weekly tab, you want

    a) some rows in the Data worksheet to be cleared, and
    [img]/forums/images/smilies/cool.gif[/img] the pivot table in the RP Weekly worksheet to be updated.

    Try this:
    <code>
    Private Sub Worksheet_Activate()
    Dim lngRow As Long

    ' Clear some rows on the Data sheet
    With Worksheets("Data")
    For lngRow = 1 To .Range("A65536").End(xlUp).Row
    If UCase(.Range("A" & lngRow)) = "S" Then
    .Range("R" & lngRow & ":Z" & lngRow).ClearContents
    End If
    Next lngRow
    End With

    ' Update pivot table
    ActiveSheet.PivotTables("PivotTable1").RefreshTabl e
    End Sub
    </code>
    Note the use of <code>.Range</code> between <code>With Worksheets("Data")</code> and <code>End With</code>. The period (point) makes the Range belong to the Data worksheet.

  15. #15
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro - If , then (Excel 2002)

    Hi Hans... this makes sense to name the Data worksheet... I've given your code a try... however, when I move from a worksheet (let's say it's the Data worksheet) and click on the "RP Weekly" worksheet the only part of the macro that actually runs is the refreshing of the pivot table... it doesn't go over to the "Data" worksheet and clear the contents of the cells defined in the macro. I was thinking since the refreshing of the pivot table macro has the word "ActiveSheet" in it, that maybe my clearing of contents function needs something like "ActiveSheet("RP Weekly")??? Of course I tried that and it didn't work. Basically, I'd like that clearing of contents part of the macro to happen when I activate the "RP Weekly" worksheet (just like the refreshing part of the macro does).
    Thanks,
    Lana

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •