Remove blanks

[redspanna]

Hi all

Can anybody suggest some code that will search down column A of my sheet and each the adjoining cell in column B is blank, remove them both...

Example data...

	A	B
1	Mr Smith	1234
2	Mr Jones	4321
3	Mr Kind
4	Mr Sad	3456
5	Mr Mr

required result for above would be..

	A	B
1	Mr Smith	1234
2	Mr Jones	4321
3	Mr Sad	3456

thanks in advance

 

[Mike LH]

Hi,

Try this code

Sub Blanks()
Dim Area As Range, LastRow As Long
Dim MyCol As String, CopyRange As Range
MyCol = "B"
  On Error Resume Next
  LastRow = Range("A:B").Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  For Each Area In Columns(MyCol).Resize(LastRow). _
                   SpecialCells(xlCellTypeBlanks).Areas
    If CopyRange Is Nothing Then
                Set CopyRange = Area.Offset(, -1).Resize(, 2)
        Else
                Set CopyRange = Union(CopyRange, Area.Offset(, -1).Resize(, 2))
        End If
  Next
  If Not CopyRange Is Nothing Then
  CopyRange.Delete
  End If
End Sub

 

[redspanna]

thank you

 

[apo]

If you have no other data in columns C on wards..

Private Sub CommandButton1_Click()
  [A1].CurrentRegion.Columns(2).SpecialCells(4).EntireRow.Delete
End Sub

 

[hiker95]

redspanna,

Here is another macro solution for you to consider that does not do any looping.

You can change the raw data worksheet name in the macro.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub DeleteColB_BlankRows()
' hiker95, 05/25/2015, ME856995
With Sheets("Sheet1")   '<-- you can change the sheet name here
  .Range("A1:B" & .Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the DeleteColB_BlankRows macro.

 

[DocAElstein]

Hi all

Can anybody suggest some code that.......

Hi Red Spanner.
. I appreciate you asked for a code, but I am always too slow and the profi’s beat me to it again… but you may want to consider a Formula…

Using Excel 2007

-	A​	B​	C​	D​
1​	Name	Number	Name Corresponding to Number withou Blank	Numbers without Blanks
2​	Mr Smith	1234​	Mr Smith	1234​
3​	Mr Jones	4321​	Mr Jones	4321​
4​	Mr Kind		Mr Sad	3456​
5​	Mr Sad	3456​	F-Tang	2​
6​	Mr Mr		OLE	9​
7​	F-Tang	2​	Wigy-Wam	10​
8​	F-Tang		Biscuit	11​
9​	OLE	9​	Barrel	12​
10​	Wigy-Wam	10​
11​	Biscuit	11​
12​	Barrel	12​

redspanna

Formulas:

Using Excel 2007

-	C​	D​
2​	=IF($D2<>"",INDEX($A$2:$A$12,SMALL(IF($B$2:$B$12=D2,ROW($B$2:$B$12)-ROW($B$2)+1),COUNTIF($D$2:D2,D2))),"")	=IFERROR(INDEX($A$2:$D$12, SMALL(IF(($B$2:$B$12)="", "", ROW($B$2:$B$12)-MIN(ROW($B$2:$B$12))+1), ROW(A1)),2),"")​

redspanna

…..
Notes:

. 1) The formulas are of the “CSE” sort which are placed in one cell and dragged down…
. 2) To put each of these formulas in turn into the spreadsheet:
. 2a) copy ( Ctrl C ) the formula complete from the above table to clipboard
. 2b) select ( click in ) the cell where the formula should go
. 2c) Hit F2 or select ( click in ) the formula bar (To be on the safe side do both!! )
. 2d) paste in the formula from the clipboard ( Ctrl V ) ( check that the formula includes a = at the start)
. 2e) now you do the famous “CSE” . – That is hold down the keys Ctrl and Shift, and hit Enter.
. 3) select the entire range C2 : D2
. 4) click and hold on the tiny black square at the right of the selection, and drag the entire row of formulae down.

Alan….
…P.s. just in case you are not sure about CSE stuff, I wasn’t.. here some light hearted notes I just made…
https://app.box.com/s/avk6paydbtame1hz7ge5zenh6ll1p35e