Macro – If , then (Excel 2002)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

Sorry about that, I messed up the indentation. It should be

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

(I have corrected my previous reply too)

Reply | Quote

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

Reply | Quote

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:

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

Reply | Quote

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”).RefreshTable
End Sub

Reply | Quote

Omit the line

ActiveSheet.Worksheet (“RP Weekly”)

It doesn’t make sense.

Reply | Quote

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”).RefreshTable
End Sub

Reply | Quote

Do you mean that

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables(“PivotTable1”).RefreshTable
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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Good point… the answer is no… it’s in the “data” worksheet.

Reply | Quote

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
the pivot table in the RP Weekly worksheet to be updated.

Try this:

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").RefreshTable
End Sub

Note the use of .Range between With Worksheets("Data") and End With. The period (point) makes the Range belong to the Data worksheet.

Reply | Quote

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

Reply | Quote

ActiveSheet(“RP Weekly”) makes no sense, but it shouldn’t be necessary to do something like that.
If the pivot table is refreshed, the code runs, including the part that clears rows depending on a condition. So perhaps there are no rows in the Data worksheet that satisfy the criteria for being cleared?

If you like, you can post a stripped down copy of your workbook, so that we can have a look at it. Make sure to remove or alter sensitive/proprietary data.

Reply | Quote

Hi Hans… I got it to work… I had not copied your exact version of the macro into mine, and in not doing so I did not have the “.” in front of the “range” part of the macro in two different areas. Of course yours worked perfectly… sorry about all the confusion, and thanks again for bearing with me through the solution!
Lana

Reply | Quote