Delet Duplicate Blanks rows

[hardeep.kanwar]

Hi! to Everyone

There are many Software and Addin and Macro or Code to Delete or
Eliminate the Duplicate Data

But, I want to Delete The Duplicate BLANKS Row.I means to Say that
Delete Every Repeated Blank row

Example

Hardeep
Blank
Renu
Blank
Blank
ABc
Blank
DEF
Blank
Blank
Blank
Now i Want in this format

Hardeep
Blank
Renu
Blank
ABC
Blank
DEF
Blank
and so on,


Thanks in Advance

Hardeep Kanwar

 

[Jacob Skaria]

Hi Hardeep

Try the below and feedback...

Sub DeleteBlankRows()
Dim lngRow As Long
Dim intBlank As Integer
For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
'Hide rows with blanks
If WorksheetFunction.CountBlank(Rows(lngRow)) = Columns.Count Then
intBlank = intBlank + 1
Else
intBlank = 0
End If
If intBlank = 2 Then Rows(lngRow).Delete: intBlank = 1
Next
End Sub

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and try it

Sub Blank_Rows()
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = lastrow To 2 Step -1
If WorksheetFunction.CountA(Rows(i)) = 0 And _
WorksheetFunction.CountA(Rows(i).Offset(-1)) = 0 Then
Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub


Mike

 

[hardeep.kanwar]

Wow thanks Jacob Skaria & Mike H

Both Works Perfectly

Is this Possible With Formula

Thanks Again

 

[Mike H]

Thanks for the feedback

Is this Possible With Formula

No.

Mike

 

[hardeep.kanwar]

Thanks for the feedback


No.

Mike


Forums' (http://www.thecodecage.com/forumz/showthread.php?t=108814)

Is This Possible With Formula or any Functions

 

[Ashish Mathur]

Hi,

You can try this. Assume that the data is in range C3:C11. In cell D3, use
the following formula and copy down till D11:

=IF(COUNTIF(D$22,MATCH(LOOKUP(REPT("z",99),C$3:C3),$C$3:$C$11,0))>=2,"a",MATCH(LOOKUP(REPT("z",99),C$3:C3),$C$3:$C$11,0)).

This will show "a" against all cases where blank rows are more than 1. Now
select range D311 and press F5 > Special. Under the formulas radio
button, select Text. This will highlight all the "a" values. Simply delete
the rows.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com