Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to set 1-row Range with Variable # of Columns (Office97 SR2)

    I use the following code to set a range where the last row number is a variable that I want to have equal to the number of records in a table (say, with 4 columns) that includes Column B:

    Dim LastRow
    LastRow = Application.Counta(ActiveSheet.Range("B:B")
    Range("A2" & LastRow).Select

    OR (say, with the number of rows saved to cell E1)

    Dim LastRow
    LastRow = ActiveSheet.Range("E1:E1")
    Range("A2" & LastRow).Select

    I want to do the same sort of thing in cases where the variable parameter is the number of Columns in the range. I have to use the 2nd of the above formulations because there are other tables that have values on the row that I would be using in a Counta("Row#:Row#") type of statement. So, lets assume that the number of Columns I need the range to span is entered into cell E1. I just do not know how to write the equivalent of "Range("A2" & LastRow).Select" where I would be using a variable LastColumn rather than LastRow.
    Any help with this would be appreciated.
    Stephen
    <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to set 1-row Range with Variable # of Columns (Office97 SR2)

    Is this what you need?

    <pre>Dim lLastRow As Long, lLastCol As Long
    lLastRow = Range("A65536").End(xlUp).Row
    lLastCol = Range("IV1").End(xlToLeft).Column
    Cells(lLastRow, lLastCol).Select
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to set 1-row Range with Variable # of Columns (Office97 SR2)

    Legare:
    I apologize for using a variable name like LastColumn. I simply want to specify the last column in a range. This is not the same as the last column with data in the particular row. Please reread the following part of my post:
    "I want to do the same sort of thing in cases where the variable parameter is the number of Columns in the range. I have to use the 2nd of the above formulations:

    Dim LastRow
    LastRow = ActiveSheet.Range("E1:E1")
    Range("A2" & LastRow).Select

    because there are other tables that have values on the row that I would be using in a Counta("Row#:Row#") type of statement. So, lets assume that the number of Columns I need the range to span is entered into cell E1. I just do not know how to write the equivalent of "Range("A2" & LastRow).Select" where I would be using a variable LastColumn rather than LastRow."
    Thank you for your patience

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to set 1-row Range with Variable # of Columns (Office97 SR2)

    This code will select the range from A1 to A1 plus the number of rows in cell E1 and plus the number of columns in cell F1. Is that closer to what you want?
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to set 1-row Range with Variable # of Columns (Office97 SR2)

    Legare:
    What code?

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to set 1-row Range with Variable # of Columns (Office97 SR2)

    OOPS, I forgot to paste in the code. Here it is:

    <pre>Dim lNumRows As Long, lNumCols As Long
    lNumRows = Worksheets("Sheet1").Range("E1").Value
    lNumCols = Worksheets("Sheet1").Range("F1").Value
    Range("A1", Range("A1").Offset(lNumRows - 1, lNumCols - 1)).Select
    </pre>

    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to set 1-row Range with Variable # of Columns (Office97 SR2)

    Legare:
    Please read Post: 68290 for more details on what I am trying to do (I generated this 2nd Post because I was not getting answers to other one -- I tried to simplify the picture but now I can see that I am still missing some links in order to use what you sent me to solve my problem.
    I need code to do the following:
    1. detect that the cell value in E1 has changed (I can write the code to save E1 to some other cell using the Paste, values option);
    2. If the cell E1 value has changed, I want the code to kick off changing about 150 range names (one for each row of the Pivot Table) so that they all incorporate the extra column (and I want these changes to be transparent to the user). I tried to use the following code (which I generated using the Tools, MACRO, Record route) but it requires the user to click "yes" 150 times which is not too cool:
    Sub Range_Name_Changes()
    Worksheets("NHF").Activate
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "CC3EYES", xlDataAndLabel
    Range("Variable_Name_Range").Select
    Selection.CreateNames Top:=False, Left:=True, Bottom:=False, Right:= _False
    End Sub

    In the above, "Variable_Name_Range" contains the entire Pivot Table (no totals on rows or Columns) and "CC3EYES" is the first variable (first row) in the Pivot Table.
    How would I incorporate what you sent me into this code so that it includes the additional column of the Pivot table and how do I shut off the "do you want to replace the existing range" thing so that the user does not have click "yes" 150 times..
    3. I would also like to make the code more generic. Currently, it starts with "CC3EYES". Can I replace this with the address of that cell (B4) and still have it keep the same range names (the names that are in the B4:B154 range)?
    I hope I have provided you with enough information and that it is clear enough.
    Thanks for your help.
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to set 1-row Range with Variable # of Columns (Office97 SR2)

    I'm afraid I don't understand PivotTables well enough to know to answer your question. Maybe someone else will jump in with that answer.

    However, the following should eliminate the 150 messages:

    <pre>Sub Range_Name_Changes()
    Application.DisplayAlerts = False
    Worksheets("NHF").Activate
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "CC3EYES", xlDataAndLabel
    Range("Variable_Name_Range").Select
    Selection.CreateNames Top:=False, Left:=True, Bottom:=False, Right:=False
    Application.DisplayAlerts = True
    End Sub
    </pre>

    Legare Coleman

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to set 1-row Range with Variable # of Columns (Office97 SR2)

    Legare:
    Really, the PT should be just another table as far as the code is concerned. I mean, consider it as just another table and if what you give does not work, I will make a copy (paste Values) of the PT.
    Also, can I use a cell address in place of "CC3eyes" and still have the range Names be the actual variable names that appear in that column?

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to set 1-row Range with Variable # of Columns (Office97 SR2)

    If a PivotTable was like any other table, then a Range would have a .PivotSelect property. I have never worked with PivotTables, and therefore do not know what the properties and methods are or do.
    Legare Coleman

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to set 1-row Range with Variable # of Columns (Office97 SR2)

    <P ID="edit" class=small>Edited by epic60sman on 29-Aug-01 17:38.</P>Legare:
    I copied the pivot table to another sheet using Paste Special Values and then I made the adjustments to the code and it worked fine. It also works with the Pivot Table.
    Thanks So Much <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

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