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


--
hardeep.kanwar
------------------------------------------------------------------------
hardeep.kanwar's Profile: http://www.thecodecage.com/forumz/member.php?userid=170
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=108814

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

--
If this post helps click Yes
---------------
Jacob Skaria


"hardeep.kanwar" wrote:


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


--
hardeep.kanwar
------------------------------------------------------------------------
hardeep.kanwar's Profile: http://www.thecodecage.com/forumz/member.php?userid=170
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=108814

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" wrote:


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


--
hardeep.kanwar
------------------------------------------------------------------------
hardeep.kanwar's Profile: http://www.thecodecage.com/forumz/member.php?userid=170
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=108814

Wow thanks Jacob Skaria & Mike H

Both Works Perfectly

Is this Possible With Formula

Thanks Again


--
hardeep.kanwar
------------------------------------------------------------------------
hardeep.kanwar's Profile: http://www.thecodecage.com/forumz/member.php?userid=170
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=108814

Thanks for the feedback

Is this Possible With Formula

No.

Mike

"hardeep.kanwar" wrote:


Wow thanks Jacob Skaria & Mike H

Both Works Perfectly

Is this Possible With Formula

Thanks Again


--
hardeep.kanwar
------------------------------------------------------------------------
hardeep.kanwar's Profile: http://www.thecodecage.com/forumz/member.php?userid=170
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=108814

Mike H;389295 Wrote:
Thanks for the feedback

Is this Possible With Formula

No.

Mike

"hardeep.kanwar" wrote:


Wow thanks Jacob Skaria & Mike H

Both Works Perfectly

Is this Possible With Formula

Thanks Again


--
hardeep.kanwar

------------------------------------------------------------------------
hardeep.kanwar's Profile: 'The Code Cage Forums - View Profile:
hardeep.kanwar'
(http://www.thecodecage.com/forumz/member.php?userid=170)
View this thread: 'Delet Duplicate Blanks rows - The Code Cage
Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=108814)




Is This Possible With Formula or any Functions


--
hardeep.kanwar
------------------------------------------------------------------------
hardeep.kanwar's Profile: http://www.thecodecage.com/forumz/member.php?userid=170
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=108814

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$2:D2,MATCH(LOOKUP(REPT("z",99),C$3:C 3),$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 D3:D11 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

"hardeep.kanwar" wrote in message
...

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


--
hardeep.kanwar
------------------------------------------------------------------------
hardeep.kanwar's Profile:
http://www.thecodecage.com/forumz/member.php?userid=170
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=108814