Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    FL
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple Macro (Excel 2002)

    I'm only a "follow-me" macro dude. I know this must be simple, but I can't figure it out. Any help would be greatly appreciated.
    I'm copying info off the internet, pasting into excel. Problem is the data fills in row 1, row 4, row 7, row 10, etc. every third row for 7 columns.
    I just want to run the mcr and delete those rows with no data.
    This works:
    Rows("2:3").Select
    Selection.Delete Shift:=xlUp
    Rows("3:4").Select
    Selection.Delete Shift:=xlUp
    But I've got between 200 and 300 rows.
    I've can't get this one to work:
    Dim i As Integer
    For i = 2 To 200
    For Each rw In Worksheets(1).Cells(i, 7).CurrentRegion.Rows
    this = rw.Cells(i + 1, 7).Value
    If this = last Then rw.Delete
    last = this
    End
    Next
    End Sub

    Any suggestion from the Gurus out dare?

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Macro (Excel 2002)

    skfravel,

    Try something like this, (I am sure the Guru's will come up with something more elegant):
    <pre>Sub RemoveBlankers()
    Dim iTotalCounter As Integer

    iTotalCounter = 0

    Range("A1").Select

    Do While iTotalCounter <= 200
    If ActiveCell <> "" Then
    ActiveCell.Offset(1, 0).Select
    Else
    ActiveCell.EntireRow.Delete
    End If
    iTotalCounter = iTotalCounter + 1
    Loop
    End Sub
    </pre>


    This sub assumes that if the first cell in the row is empty then the entire row is also empty. If this is not the case, then you could add lines to test for data in the row if the first cell is empty.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Macro (Excel 2002)

    You could also try this - from <!profile=LegareColeman>LegareColeman<!/profile> WMVP:<pre>Public Sub DeleteBlankRows()
    Dim lLastRow As Long, i As Long, j As Integer
    Dim strWk As String
    lLastRow = Cells(Application.Rows.Count, Columns("A").Column).End(xlUp).Row - 1
    For i = lLastRow To 0 Step -1
    strWk = ""
    For j = 0 To 25
    strWk = strWk & ActiveSheet.Range("A1").Offset(i, j)
    Next j
    If strWk = "" Then
    Range("A1").Offset(i, 0).EntireRow.Delete
    End If
    Next i
    End Sub</pre>

    HTH
    Gre

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Simple Macro (Excel 2002)

    Why not
    1) use a temp column to 'number' the rows (to preserve order)
    2)sort the datarange to put all the blank rows to the bottom
    3) then delete all the blanks in one go
    4) resort on your temp column to original order
    5) get rid of your temp column

    ..I'm sure this would be faster if you had a lot of rows to process.

    zeddy

  5. #5
    Lounger
    Join Date
    Oct 2003
    Location
    Birmingham, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Macro (Excel 2002)

    Since you're a follow me macro dude, record a macro thus-wise:
    start recording
    select a whole column where there is always some data unless the whole row is blank
    Apply autofilter to that column
    Criterion = look for blanks
    Select whole sheet ie all the blank rows
    Edit | Delete Row
    Click anywhere
    Stop recording

    You could attach this macro to a button, or run it from a shortcut.
    It wouldn't depend on a constant number of blank rows between the rows you want.

Posting Permissions

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