------ Register to get rid of these "In Post" ads! ------


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
'Original Source: The Code Cage Forums http://www.thecodecage.com/forumz/worksheet-functions/108814-delet-duplicate-blanks-rows.html#post389243

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

------ Register to get rid of these "In Post" ads! ------


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
'Original Source: The Code Cage Forums http://www.thecodecage.com/forumz/worksheet-functions/108814-delet-duplicate-blanks-rows.html#post389255
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/members/hardeep-kanwar.html
> View this thread: Delet Duplicate Blanks rows
>
>

------ Register to get rid of these "In Post" ads! ------


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
'Original Source: The Code Cage Forums http://www.thecodecage.com/forumz/worksheet-functions/108814-delet-duplicate-blanks-rows.html#post389256
> 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/members/hardeep-kanwar.html
> View this thread: Delet Duplicate Blanks rows
>
>

------ Register to get rid of these "In Post" ads! ------


Wow thanks Jacob Skaria & Mike H

Both Works Perfectly

Is this Possible With Formula

Thanks Again

------ Register to get rid of these "In Post" ads! ------


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/members/hardeep-kanwar.html
'Original Source: The Code Cage Forums http://www.thecodecage.com/forumz/worksheet-functions/108814-delet-duplicate-blanks-rows.html#post389295
> View this thread: Delet Duplicate Blanks rows
>
>

------ Register to get rid of these "In Post" ads! ------



Is This Possible With Formula or any Functions

------ Register to get rid of these "In Post" ads! ------


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: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 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" <hardeep.kanwar.3u688p@thecodecage.com> wrote in message
news:hardeep.kanwar.3u688p@thecodecage.com...
>
> 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/members/hardeep-kanwar.html
> View this thread:
'Original Source: The Code Cage Forums http://www.thecodecage.com/forumz/worksheet-functions/108814-delet-duplicate-blanks-rows.html#post389360
> Delet Duplicate Blanks rows
>