If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > Macro for deleting whole rows

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 05-04-12, 16:10
latouffe latouffe is offline
Registered User
 
Join Date: May 2012
Posts: 1
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 16:16.
Reply With Quote
  #2 (permalink)  
Old 05-09-12, 02:23
JerryDal JerryDal is offline
Registered User
 
Join Date: Jan 2002
Location: Bay Area
Posts: 495
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
File Type: zip DeleteWholeRowsDemo.zip (9.2 KB, 6 views)

Last edited by JerryDal; 05-09-12 at 02:27.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On