3 Replies - 3435 Views - Last Post: 30 August 2012 - 08:29 PM Rate Topic: -----

#1 jae&devon  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 61
  • Joined: 24-October 09

write theMacro so that it runs until it sees a blank

Posted 07 August 2012 - 08:34 AM

I have created the below macro, which we want to use for these reports that we extract out our system daily. The issue is the data that is extracted may be 50 rows one day and 55 or 45 the next day. I know this line "Sheet1!R1C1:R32659C35",is the one that is controling the number of rows it includes in the report. How do i get this macro to loop until it finds an empty row?

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R32659C35", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("Sheet4").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("GL Company Unit")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("GL Company Unit Alias" _
        )
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("CDM")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Out. MTD Total Units"), _
        "Count of Out. MTD Total Units", xlCount
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Out. YTD Total Units"), _
        "Count of Out. YTD Total Units", xlCount
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Inp. MTD Total Units"), _
        "Count of Inp. MTD Total Units", xlCount
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Inp. YTD Total Units"), _
        "Count of Inp. YTD Total Units", xlCount
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("MTD Total Units"), "Count of MTD Total Units", _
        xlCount
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("YTD Total Units"), "Count of YTD Total Units", _
        xlCount
End Sub



Is This A Good Question/Topic? 0
  • +

Replies To: write theMacro so that it runs until it sees a blank

#2 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: write theMacro so that it runs until it sees a blank

Posted 07 August 2012 - 12:30 PM

You might like this trick, which you can do programmatically. If you press the end key in excel, and then press the down key, you will do the following:

1. If the cell you are in is blank, you will go to the next filled cell.
2. if it isn't, and the one below you is blank, you will go to the next filled cell.
3. If it isn't, and the one below you is also filled, you will go to the last filled cell before a blank one.

So, you can go to the top of your range, hit the end key, hit the down key, and you will be at the bottom of your range, one cell above the blank cell you are looking for with your rather more complicated solution.
Was This Post Helpful? 0
  • +
  • -

#3 jae&devon  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 61
  • Joined: 24-October 09

Re: write theMacro so that it runs until it sees a blank

Posted 08 August 2012 - 08:27 AM

I do like that trick. I learn something new everyday. I hit end and then pagedown and it only went to line34, mind you I have about 3000+ lines in this report. Also not sure if I mentioned it before I am running a macro while creating my piviot table. Not sure if that makes a difference.
Was This Post Helpful? 0
  • +
  • -

#4 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: write theMacro so that it runs until it sees a blank

Posted 30 August 2012 - 08:29 PM

you have to hit end and down arrow, not pgdn. The four arrow keys work this way. Goes all the way back to Lotus 1-2-3 in 1982. :)

This post has been edited by BobRodes: 30 August 2012 - 08:30 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1