Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jun 2001
    Location
    Burlington, Ontario, Canada
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Variable unknown rows (Excel 97 SR1 NT4)

    Greetings all.

    In the process of trying to code a long transaction macro I have a general (and hopefully) simple question to the gallery before I begin.

    I have 4 product groups that I will be calculating discounts, one after the other.
    Before I do that I have to prep the sheet and find the last row for the whole data and then begin with each product group, pulled from data filter. So my first section, the prep is fine:
    Dim lRow As Long
    lRow = Cells(Application.Rows.Count, Columns("B").Column).End(xlUp).Row

    But each group will be identified from the filter row in Column G - and the length will be unknown. If I apply the same coding - ie
    Dim lRowA '(product A - Last Row)
    Selection.AutoFilter Field:=7, Criteria1:="=A-*", Operator:=xlAnd
    lRowA = Cells(Application.Rows.Count, Columns("G").Column).End(xlUp).Row
    then onto lRowA, lRowB, lRowC, etc
    As the filter will only show visible applicable cells, the original lRow would not be applicable.

    So -
    Will my value for the original lRow change? As I am still doing calculations before and after these sorts, I would need to keep that lRow value constant.

    Or is there an easier way to get last row for the sheet and each of the four variable last rows without changing their respective numbers?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Variable unknown rows (Excel 97 SR1 NT4)

    If the selection data is in column G and is fltered, can you use a variation on:

    for each rngCell in Columns("G:G").SpecialCells(xlCellTypeVisible)
    rngCell.Offset(row, column).<method...>
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Lounger
    Join Date
    Jun 2001
    Location
    Burlington, Ontario, Canada
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable unknown rows (Excel 97 SR1 NT4)

    Vunderbar!
    Simple, yet effect.
    That did the trick.

    Thanks.

  4. #4
    Lounger
    Join Date
    Jun 2001
    Location
    Burlington, Ontario, Canada
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable unknown rows (Excel 97 SR1 NT4)

    Ooops. Spoke to soon. It seemed to work wonderfully for one stage. In another stage, using similar coding, I asked it to write a formula to one cell, then to another cell.

    The problem is, is that Excel is writing the formulas down the entire column! I thought for each row select:
    For Each rs In Columns("G:G").SpecialCells(xlCellTypeVisible)
    that only the rows affected by the filter would be calculated upon. This is not the case. Once the formula is copied down to the bottom of the filtered data shown, it keeps on going.

    How do I say visible cells only with value?
    I used: if range(rc,"G")<>"" then ...
    but that, and any variation to identify just cells with a value come up in error.
    Am I just missing a simple syntax or something altogether?! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Variable unknown rows (Excel 97 SR1 NT4)

    Apologies, I'm the one who should say "oops".Obviously that method I proposed would select all the blank cells. The SpecialCells method can be "stacked" like this:

    For Each rngCell in Columns("G:G").SpecialCells(xlCellTypeConstants, 1).SpecialCells(xlCellTypeVisible)

    Adjust the first SpecialCells(argument) according to the content of your column G, formulas, text, numbers, etc. Review all the possible SpecialCell arguments in XL VBA Help, and you can also use the Edit, GoTo, Special dialog as a reminder of all the possibilities. (Recording macros using GoTo, Special is a quick way to learn how to use SpecialCells.)

    It's possible that SpecialCells may not fit the bill in which case you'd have to go back to your original approach.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Lounger
    Join Date
    Jun 2001
    Location
    Burlington, Ontario, Canada
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable unknown rows (Excel 97 SR1 NT4)

    After banging my head against a brick wall trying to figure out why this thing wasn't working, one simple change in syntax (and approach did the trick):

    For Each PRow In Columns("H:H").SpecialCells(xlCellTypeVisible)
    If PRow<> "" Then ....
    ... End If
    Next PRow
    and that ladies and gentlemen solved the dilemma. I am STILL slapping myself for not clueing into that simple arrangment sooner.

    I thank you for your assistance in pointing me the right way. <img src=/S/thumbup.gif border=0 alt=thumbup 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
  •