Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mark double rows (WinXP / Excell2003 NL)

    I'm importing CSV text files of variable length, generated by a third party application, into an Excel sheet. Because the users of the sheet are not very familiar with Excel, I created a toolbar with a button for each column to sort by. These buttons set a global variable strSortedBy to the column to sort by (A-F). Then there's a button that calls a Sub to mark, in red, "double" rows that have the same value in the cells in the sorted by column. This works, but I would like to be able to unmark by clicking on the same button again. So I added the line lngColor = 3 - lngColor, but that fails; the rows stay red. What am I doing wrong?

    Public strSortedBy As String

    Sub MarkDouble()
    Dim lngColor As Long
    Dim lngRow As Long
    lngColor = 3 - lngColor ' Toggle between red (3) and black (0)
    For lngRow = Range(strSortedBy & "65536").End(xlUp).Row To 2 Step -1
    If Range(strSortedBy & lngRow) = Range(strSortedBy & (lngRow - 1)) Then
    Range(strSortedBy & lngRow).EntireRow.Font.ColorIndex = lngColor
    Range(strSortedBy & (lngRow - 1)).EntireRow.Font.ColorIndex = lngColor
    End If
    Next lngRow
    End Sub

    I have a second issue. When a text file is imported, keying Ctrl+End should jump to the last cell in the sheet that contains data, i.e. F3229. But instead it jumps to i.e. BT3229 for some reason unknown to me. Clearing the sheet and importing another file the last cell is i.e. F2886, but Ctrl+End still jumps to, in this case, BT3229. Saving the now empty sheet before importing the next file doesn't help. What is causing this and how can it be "reset" to jump to the right location?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mark double rows (WinXP / Excell2003 NL)

    Change:
    Dim lngColor As Long
    To:
    Static lngColor As Long

    That way lngColor keeps it value.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mark double rows (WinXP / Excell2003 NL)

    What happens if you select columns G:BT and select Edit | Clear | All, then save the workbook?

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mark double rows (WinXP / Excell2003 NL)

    Thanks Jan Karel, that did the trick of course. I'm familiar with Static, so I should have noticed that myself... <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mark double rows (WinXP / Excell2003 NL)

    I tried that a couple of times before I asked here yesterday, but that didn't work. Reading my original post I see that I didn't mention clearing before saving. To rule out anything weird going on in the computer, I even rebooted the PC, but no joy.
    This morning however, it all works fine! No idea what happened yesterday. Maybe my PC needed a good nights sleep as much as I did...

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Marietta, Georgia, USA
    Posts
    296
    Thanks
    9
    Thanked 4 Times in 4 Posts

    Re: Mark double rows (WinXP / Excell2003 NL)

    This kind of FlaKeY WinDoZe stuff is why I have started playing around with Linux.

    Now all I need is something like Visual Basic for development in Linux..
    Gambas is a good start ... GAMBAS = Gambas Almost Means BASic
    Rick Groszkiewicz
    Life is too short to drink bad wine (or bad coffee!)

Posting Permissions

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