Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    selecting the current row (XL 97 sr2 on Win 2K)

    I want to work my way down some rows and clear certain cells in the current row if the counts in that row exceeds a maximum value. In pseudo code it would be something like:

    set max to COUNTA of row 1
    for each of the remaining rows the current region
    if COUNTA of active row > max then
    clear from cell C to the last used cell in the active row
    put some text (e.g. "values cleared" ) into cell C
    colour the text in cells A to C red
    endif
    next row

    Any suggestions / alternatives?

    I'm sure this must be trivial but my VBA knowledge is so limited.

    stuck

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

    Re: selecting the current row (XL 97 sr2 on Win 2K)

    Does this do what you want:

    <pre>Option Explicit
    Public Sub DelRows()
    Dim lFRow As Long, lLRow As Long, I As Long, lCount As Long, lMax As Long
    Dim J As Long, lCnt As Long
    lFRow = ActiveCell.CurrentRegion.Row - 1
    lLRow = ActiveCell.CurrentRegion.Row + ActiveCell.CurrentRegion.Rows.Count - 2
    lMax = 0
    For I = 0 To 255
    If Range("A1").Offset(lFRow, I).Value <> "" Then lMax = lMax + 1
    Next I
    For I = lLRow To lFRow + 1 Step -1
    lCnt = 0
    For J = 0 To 255
    If Range("A1").Offset(I, J).Value <> "" Then lCnt = lCnt + 1
    Next J
    If lCnt > lMax Then
    Range(Range("C1").Offset(I, 0), Range("IV1").Offset(I, 0)).Clear
    Range("C1").Offset(I, 0).Value = "Values cleared"
    Range("A1:C1").Offset(I, 0).Font.ColorIndex = 3
    End If
    Next I

    End Sub
    </pre>

    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: selecting the current row (XL 97 sr2 on Win 2K

    eee gods that is scary but yes it did what I want!

    I'm still looking into how it works, meanwhile thanks.

    (un)stuck (but left wondering if I'll ever get the hang of VBA)

Posting Permissions

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