Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    finding the bottom of a lit (xp)

    I have a macro in which copies a list from another page. after the copy I want to find the last row with a number in the left column and del all the rows under it. now the boxes in purple still have info in them "". so I was wondering if there was any way to go down to the last row wit a number value and then del everything else under it. I want to do this so I can properly sort the list. sorting it now desending puts all the blank spots on the top.

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

    Re: finding the bottom of a lit (xp)

    You could use this function (copy into a module):

    Function GetLastNumRow(TheCol)
    Dim oCell As Range
    Set oCell = Cells(65536, TheCol).End(xlUp)
    Do While Not IsNumeric(oCell)
    Set oCell = oCell.Offset(-1, 0)
    Loop
    GetLastNumRow = oCell.Row
    End Function

    For example, if your data are in column C, use

    Dim lngRow As Long
    lngRow = GetLastNumRow("C") + 1
    Range(lngRow & ":65536").Delete

    You can also use
    lngRow = GetLastNumRow(3) + 1

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding the bottom of a lit (xp)

    I have been trying this for a hour. Here is a copy of the chart I am working on. what I want to do is write some code for a button ( I have removed the button to make this simple. I want to got down the list and find all the cells in purple.... all of thos rows I want to del. now every time I copy this list in from another page the list may be longer or shorter. I have tried the code from Hans but I must be getting tired as I could not get it to work.

    help me please

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

    Re: finding the bottom of a lit (xp)

    The cells you're interested in are in column D, so it makes little sense to apply the code to column P. Moreover, you have modified the GetLastNumRow function to always start in cell P29, regardless of the argument passed to it. That makes even less sense.

    Here is the correct code:

    Function GetLastNumRow(TheCol)
    Dim oCell As Range
    Set oCell = Cells(65536, TheCol).End(xlUp)
    Do While Not IsNumeric(oCell)
    Set oCell = oCell.Offset(-1, 0)
    Loop
    GetLastNumRow = oCell.Row
    End Function

    Private Sub Remove()
    Dim lngRow As Long
    lngRow = GetLastNumRow("D") + 1
    Range(lngRow & ":65536").Delete
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding the bottom of a lit (xp)

    I had moved the listing closer to the left so P changed to D... that is where that part came from.... as for the other chage I coud not get it to go to the bottom of the list with the

    Cells(65536, TheCol).End(xlUp) portion so I was trying to get it to go right to the bottom of the column.
    row 28 would be the bottom all the time.... so I was trying to get it to go there...

    I think I see my problem Hans

    I was going back to the worksheet looking for the highlighted box to move to the bottom and start to move up when I was stepping through the code. I did not give it enough time... I am sorry for jumping the gun.....I am running on little sleep and a lot of stress right now... thanks a lot Hans

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

    Re: finding the bottom of a lit (xp)

    The code I posted doesn't select any cell or range. In most situations, it isn't necessary to select ranges, and code runs more efficiently if you don't select ranges.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: finding the bottom of a lit (xp)

    Hans,
    Why did you make the sub procedure private? Is it not supposed to be run from the ALT+F8 dialog?
    Regards,
    Rudi

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

    Re: finding the bottom of a lit (xp)

    I didn't make it private - it was that way in the workbook Cory attached, I only modified the code. There doesn't seem to be a good reason for it being Private.

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: finding the bottom of a lit (xp)

    Sorry, i didn't look at the original attachment. I just assumed ther was a reason and wanted to know why!
    Tx
    Regards,
    Rudi

Posting Permissions

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