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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Delet Duplicate Blanks rows
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 |
#2
|
|||
|
|||
Delet Duplicate Blanks rows
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 |
#3
|
|||
|
|||
Delet Duplicate Blanks rows
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 |
#4
|
|||
|
|||
Delet Duplicate Blanks rows
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 |
#5
|
|||
|
|||
Delet Duplicate Blanks rows
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 |
#6
|
|||
|
|||
Delet Duplicate Blanks rows
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 |
#7
|
|||
|
|||
Delet Duplicate Blanks rows
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: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 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 "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 |
Thread Tools | |
Display Modes | |
|
|