Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    writing macros (microsoft excel 2000)

    I am having some issues writing a macro. What I want the macro to do is: go down a column and erase all the numbers above a certain limit, and then have the empty cells deleted so that there are no empty cells in the column. ANy yhelp would be greatly appreciated. thanks a ton.

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

    Re: writing macros (microsoft excel 2000)

    First, your message is not very specific so I don't know what sheet the column is on, which column, or what the limit above which you want to delete the cells. The code below assumes you want column A on the active sheet and you want to delete values greater than 10. The code needs to work from the bottom of the column up to keep from missing consecutive values less than the limit.

    <pre>Public Sub DelBelowLimit()
    Dim I As Long
    For I = ActiveSheet.Range("A65536").End(xlUp).Row - 1 To 0 Step -1
    If ActiveSheet.Range("A1").Offset(I, 0).Value > 10 Then
    ActiveSheet.Range("A1").Offset(I, 0).Delete (xlShiftUp)
    End If
    Next I
    End Sub
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: writing macros (microsoft excel 2000)

    In a spreadsheet I use a formula in column A to find duplicates and mark them as 'dup'. I changed the line in your code which reads Value > 10 to Value ="dup". this runs OK and deletes 'dup' from column A. Is there a simple way in your coding to delete the row in which 'dup' appears. I think that it has something to do with setting the ActiveSheet.Range but asI have limited knowldege in VBA I do need assistance.
    Thanks in advance.

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

    Re: writing macros (microsoft excel 2000)

    To delete the row where "dup" appears in column A, use this code:

    <pre>Public Sub DelBelowLimit()
    Dim I As Long
    For I = ActiveSheet.Range("A65536").End(xlUp).Row - 1 To 0 Step -1
    If ActiveSheet.Range("A1").Offset(I, 0).Value = "dup" Then
    ActiveSheet.Range("A1").Offset(I, 0).EntireRow.Delete
    End If
    Next I
    End Sub
    </pre>

    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
  •