Blank rows deletionClosed

Question

Hello, 

Dear friends,

I really appreciate your support and help all the time.

I have another problem and I need your help once again. I have files with name Jan 2010, Feb 2010............Dec 2010 (12 files). I am using a macro which search rows contain specific word according to my desire and copy searched rows in another worksheet "Records". 

Searched rows from Jan 2010 starts copying from row # 1 in Worksheet "Records" the searched rows from Feb 2010 starts copying from row # 1000 in worksheet "Records" and so on. The searched rows copied in worksheet "Records" with gape of 1000 rows. I need a macro which work on worksheet "Records" leave one blank row between the data / rows  and delete all other blank rows between data of months. Just to show separation of data of each month.

e.g. Jan's data then one blank row then Feb's data then one blank row then Mar's data then a blank row and so on.

I will be very grateful if someone can help me.

Thank you,

Aslam 



Configuration: Windows XP / Safari 535.1

venkat1926 · Answer

copy the data in sheet 1 in some other location e.g. sheet 2 also as precaution


now try this macro on sheet1

Sub test()
Dim r As Range, j As Long, r1 As Range
j = Cells(Rows.Count, "A").End(xlUp).Row
Set r = Cells(j, 1)
Do
Set r1 = r.End(xlUp)
Range(r.End(xlUp).Offset(-1, 0), r1.End(xlUp).Offset(2, 0)).EntireRow.Delete
Set r = r1.End(xlUp)
If r.Row = Range("A1").End(xlDown).Row Then Exit Do
Loop
End Sub

venkat1926 · Answer

quote 
ust need one amendment in this, that the macro will work only on sheet1 of workbook "Records" not on all open workbooks. 
unquote 


First  save the workbook safely some where to recover if something goes wrong. 
see this macro 

Sub test() 
Dim r As Range, j As Long, r1 As Range, k As Long 
For k = 1 To Worksheets.Count 
With Worksheets(k) 
j = .Cells(Rows.Count, "A").End(xlUp).Row 
Set r = .Cells(j, 1) 
Do 
Set r1 = r.End(xlUp) 
Range(r.End(xlUp).Offset(-1, 0), r1.End(xlUp).Offset(2, 0)).EntireRow.Delete 
Set r = r1.End(xlUp) 
If r.Row = .Range("A1").End(xlDown).Row Then Exit Do 
Loop 
End With 
Next k 
End Sub

venkat1926 · Answer

can you please post a very small extract of atleast 2 data sheets

venkat1926 · Answer

take aquarelle'S suggestion

venkat1926 · Answer

thank you for uploading the two files. in your first message you talk about worksheet named "records". there is no such sheet in either of your two files

I suggest re frame your questions with respect to these two files.

venkat1926 · Answer

Aslam there appears to be a SPAM reply. inform administrator 

however your request
"just keep one row blank row after records of each month to make it seperate, rest blank rows should be deleted through macro. The macro should work till last row of the worksheet. "

try this macro 

Public Sub DeleteRow()

' DeleteRow Macro
'
' Keyboard Shortcut: Ctrl+j

Dim x As Long
Dim y As Long
x = Range("C65536").End(xlUp).Row

For y = x To 2 Step -1

If Cells(y, 2).Value = "" And Cells(y, 3).Value = "" And Cells(y, 4).Value = "" Then

Rows(y).Delete

End If

Next y
For y = 2 To x
If Cells(y, "B") <> "" Then Rows(y + 1).Insert
Next y


End Sub

Blank rows deletion

6 responses

Similar discussions

Subscribe To Our Newsletter!