Results 1 to 2 of 2
  1. #1
    Join Date
    May 2012
    Posts
    1

    Unanswered: Macro for deleting whole rows

    Hi!

    I tried to create a macro for deleting specific cells in the "A" column but its not working for the moment. And i think I need your help. I want to make flashcards (to learn english) and i need a special format to upload them into a software.

    I have something like this in my column A (each word/sentence and blank is a cell)
    quivered


    Created: 11/21/11 1:19 AM




    (p.115)



    ushered


    Created: 11/21/11 1:23 AM




    (p.115)



    trod


    Created: 11/21/11 1:24 AM




    (p.116)



    Varys

    And i want to create something like this:
    quivered


    ushered


    trod


    Varys


    So, the macro should delete this
    Created: ***********
    (blank)
    (blank)
    (blank)
    (blank)
    (p.***)
    (blank)
    (blank)
    (blank)

    i hope you understand me. I just want to leave 2 blank rows between words.

    Thank you!
    Last edited by latouffe; 05-04-12 at 17:16.

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Attached is an Excel 2003 workbook and VBA code (a macro) to do this task.
    Steps to solve this:
    1) delete all rows where column A cell starts with the text "Created:" or "(p.",
    and delete the row if cell in column A is blank
    2) for as many rows as there is text in column A cell, insert 2 following blank rows

    Code:
    Sub Format_Sheet1()
    Dim i As Long, lastRow As Long
    Dim wks As Worksheet
    Dim deleteRow As Boolean, allDeleted As Boolean
    Dim tmp As String
    'Discard blank rows and rows that start with "Created:" or "(p." in column A
    'With rows that remain, insert 2 blank rows after each row that has a value in column A
    
        Set wks = Sheets("Sheet1")
           
        Application.ScreenUpdating = False
        lastRow = Range("A65536").End(xlUp).Row
        
        With Sheets(wks.Name)
            'stop deleting rows after cell with "(The End)" reached
            .Cells((lastRow + 1), 1).Value = "(The End)"
            
            'do the deletes
            For i = 1 To lastRow
                deleteRow = False
                tmp = Trim(.Cells(i, 1).Value)
                If tmp = "" Then
                    deleteRow = True
                ElseIf Left(tmp, 8) = "Created:" Then
                    deleteRow = True
                ElseIf tmp = "(The End)" Then
                    deleteRow = True
                    allDeleted = True
                Else
                    If Left(tmp, 3) = "(p." Then
                        deleteRow = True
                    End If
                End If
                If deleteRow Then
                    .Cells(i, 1).EntireRow.Delete
                    i = i - 1
                End If
                If allDeleted Then Exit For
            Next
            
            'do the inserts
            i = 1
            Do While .Cells((i + 1), 1).Value <> ""
                Rows((i + 1) & ":" & (i + 2)).Select
                Selection.Insert Shift:=xlDown
                i = i + 3
            Loop
            Range("A1").Select
        End With
        
        Application.ScreenUpdating = True
        
        Set wks = Nothing
        
    End Sub
    Attached Files Attached Files
    Last edited by JerryDal; 05-09-12 at 03:27.

Posting Permissions

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