Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Jan 2005
    Location
    Colorado Springs, Colorado, USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting cell > 0 in column (VB in Excel (2000))

    Yes I'm new at this, so don't laugh too loud, other posters are trying to sleep.

    I am trying to select rows after sorting them in desending order and then selecting all the rows, with a value in a selected cell greater then 0 (zero). Something like... Rows("1:[NUMBER OF ROWS GREATER THEN 0").Select

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

    Re: Counting cell > 0 in column (VB in Excel (2000))

    Does this do what you want?

    <pre>Dim lGT0
    lGT0 = Application.WorksheetFunction.CountIf(ActiveSheet. Range("A:A"), ">0")
    ActiveSheet.Range(Range("A1"), Range("A1").Offset(lGT0 - 1, 0)).Select
    </pre>

    Legare Coleman

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

    Re: Counting cell > 0 in column (VB in Excel (2000))

    This macro will do what you want, I think. Change the constant lngCol to the number of the column on which you want to check.

    Sub SelectPositive()
    Const lngCol = 1 ' column A
    Dim lngRow As Long
    lngRow = 1
    Do While Cells(lngRow, lngCol) > 0
    lngRow = lngRow + 1
    Loop
    If lngRow > 1 Then
    lngRow = lngRow - 1
    Range("1:" & lngRow).Select
    End If
    End Sub

  4. #4
    Lounger
    Join Date
    Jan 2005
    Location
    Colorado Springs, Colorado, USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting cell > 0 in column (VB in Excel (2000))

    Thanks much!

    But is there anyway to select the complete rows not just the cells?

    I am assuming that "Range("A:A") is the column I am checking

  5. #5
    Lounger
    Join Date
    Jan 2005
    Location
    Colorado Springs, Colorado, USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting cell > 0 in column (VB in Excel (2000))

    This work but will I run into problems? (Please critique)

    Sub SortAndCopyNeededLabels()

    Dim lGT0 As Integer
    Windows("BAUB Main Databank.xls").Activate
    lGT0 = Application.WorksheetFunction.CountIf(ActiveSheet. Range("C:C"), ">0")
    Cells.Select
    Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Rows("1:" & lGT0).Select
    Selection.Copy
    End Sub

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

    Re: Counting cell > 0 in column (VB in Excel (2000))

    Are you going to add more code? In general, Excel VBA code is more efficient if you don't select ranges if it isn't necessary. for example,

    Rows("1:" & lGT0).Select
    Selection.Copy

    can be replaced with

    Rows("1:" & lGT0).Copy

    and if you're going to paste the rows elsewhere, you can do it in one fell swoop:

    Rows("1:" & lGT0).Copy Destination:=Worksheets("Another Sheet").Range("A4")

  7. #7
    Lounger
    Join Date
    Jan 2005
    Location
    Colorado Springs, Colorado, USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting cell > 0 in column (VB in Excel (2000))

    Twenty thousand years ago I won special national recognition in a contest for writing a program is BASIC. Shortly after that I quit programming. Things have changed so much since then. This is cool stuff you are showing me, and efficient

    The "Worksheets" is going to be a "new worksheet" I have to create to paste this data into, to send to a barcode label program.

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

    Re: Counting cell > 0 in column (VB in Excel (2000))

    Basic has come a long way since the early days...

    Excel empties the clipboard after most actions, so you will have to create your new worksheet before copying the rows to the clipboard. You can then use the construction with

    thisrange.Copy Destination:=thatrange

    If you would copy the rows to the clipboard, then create a new worksheet, there wouldn't be anything to paste.

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

    Re: Counting cell > 0 in column (VB in Excel (2000))

    Like this:

    <pre>Dim lGT0
    lGT0 = Application.WorksheetFunction.CountIf(ActiveSheet. Range("A:A"), ">0")
    ActiveSheet.Range(Range("A1"), Range("A1").Offset(lGT0 - 1, 0)).EntireRow.Select
    </pre>


    Yes, Range("A:A") is the range you are checking.

    You can also use what Hans said with this by changing the Third line to something like this:

    <pre> ActiveSheet.Range(Range("A1"), Range("A1").Offset(lGT0 - 1, 0)).EntireRow.Copy Destination:="where you want it copied to"
    </pre>


    My code is a little shorter, and I think a lot faster than what Hans did (since I didn't see him online for a few hours earlier, he must have been to a party <img src=/S/grin.gif border=0 alt=grin width=15 height=15>).
    Legare Coleman

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

    Re: Counting cell > 0 in column (VB in Excel (2000))

    Using the CountIf worksheet function will certainly be a lot faster than looping.

    And I had a wonderful evening, thank you!

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

    Re: Counting cell > 0 in column (VB in Excel (2000))

    And you didn't invite us! <img src=/S/aflame.gif border=0 alt=aflame width=16 height=16>
    Legare Coleman

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

    Re: Counting cell > 0 in column (VB in Excel (2000))

    It's a bit far, don't you think? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Counting cell > 0 in column (VB in Excel (2000))

    Only about a 12 hour trip from here. <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Legare Coleman

Posting Permissions

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