How to quickly filter out blank cells in multiple rows & columns?

[yandy]

Hi,

I need help on filtering out blank cells in multiple rows & columns in a fast way on excel.


Attached an excel file for example. Only the cells with value are expected to be remained. Would be even great to have all values showing on one column like the table.




A


1
6_5


2
7_5


3
7_6


4
11_9


5
11_10


6
13_1


7
14_13

[Aussiebear]

Interesting spreadsheet.... No column or Row headers. Does the randomness of the data mean anything at all?

[yandy]

hi, column & row headers are just number in sequence, e.g. 1,2,3,4,5,6,7, etc.


6_5 represents the 6th column and 5th row

[Aussiebear]

Assuming you have access to Power Query.

Select Data Tab>get & Transform, and Select FromTable/Range.
You will. now be asked to create a Table.
the Range you wish to edit and click OK.
Select RemoveRows> Remove Blank Rows.
Close & Load (This sends your data to a New Sheet).
Close the Query Editor.

Once this has occurred, you can simply repeat the process but replace Remove Rows with Remove Columns>Remove Blank Columns etc.

[Paul_Hossler]

Try this



Option Explicit


Sub DeleteEmpty()
Dim r As Long, c As Long
Dim rLast As Range, rData As Range, rEnd As Range

Application.ScreenUpdating = False

With ActiveSheet

Set rLast = .Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

Set rData = Range(.Cells(1, 1), rLast)

With rData

'remove empty strings to make real empty cells
.Replace What:="", Replacement:="###", LookAt:=xlPart
.Replace What:="###", Replacement:="", LookAt:=xlPart

'delete empty columns
For c = .Columns.Count To 1 Step -1
If Application.WorksheetFunction.CountBlank(.Columns(c)) = .Rows.Count Then
.Columns(c).Delete
End If
Next c

'delete empty rows
For r = .Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountBlank(.Rows(r)) = .Columns.Count Then
.Rows(r).Delete
End If
Next r
End With

Set rLast = .Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

Set rData = Range(.Cells(1, 1), rLast)


'move up data cells6
For c = 1 To rData.Columns.Count
Set rEnd = rData.Columns(c).Cells(.Rows.Count, c).End(xlUp)

On Error Resume Next
Range(.Cells(1, c), rEnd).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
On Error GoTo 0

Next c


End With


Application.ScreenUpdating = False


End Sub

[georgiboy]

Perhaps the below will get them into one column:

Sub test()
Dim rng As Range, rCell As Range, var As Variant, x As Long, rCount As Long

Set rng = Sheet1.UsedRange
With rng
.Replace "", "|"
.Replace "|", ""
Set rng = .SpecialCells(xlCellTypeConstants)
rCount = .Cells.Count
End With
ReDim var(rCount - 1)
For Each rCell In rng
var(x) = rCell
x = x + 1
Next rCell
Range("A1").Resize(rCount) = Application.Transpose(var)
End Sub

Not sure if they had to be in a specific order

[georgiboy]

If you want the data sorted the other way then you could add a blank sheet 'Sheet2' in the example below and try the below:

Sub test()
Dim rng As Range, rCell As Range, var As Variant, x As Long, rCount As Long, tVar As Variant

Sheet2.Cells.ClearContents
Set rng = Sheet1.UsedRange
tVar = rng.Value
Sheet2.Range("A1").Resize(UBound(tVar, 2), UBound(tVar)) = Application.Transpose(tVar)
Set rng = Sheet2.UsedRange

With rng
.Replace "", "|"
.Replace "|", ""
Set rng = .SpecialCells(xlCellTypeConstants)
rCount = .Cells.Count
End With
ReDim var(rCount - 1)
For Each rCell In rng
var(x) = rCell
x = x + 1
Next rCell
Sheet1.Range("A1").Resize(rCount) = Application.Transpose(var)
End Sub

[georgiboy]

This is a better option than post 7:

Sub test()
Dim rng As Range, var As Variant, oVar() As Variant
Dim r As Long, c As Long, z As Long

Set rng = Sheet1.UsedRange
ReDim oVar(Application.CountIf(rng, "?*") - 1)
var = rng.Value
For c = 1 To UBound(var, 2)
For r = 1 To UBound(var)
If Len(var(r, c)) > 0 Then
oVar(z) = var(r, c): z = z + 1
End If
Next r
Next c
Range("A1").Resize(UBound(oVar) + 1) = Application.Transpose(oVar)
End Sub

[Aflatoon]

If you have 365:
=LET(d,TOCOL(F1:AB27),FILTER(d,d<>""))

[yandy]

Thanks all for the suggestions.

I was able to do it in an easy way in power query to unpivot the columns :)

But also good to know more methods to filter out blank cells for multiple rows & columns :)

[Aussiebear]

Good to know you came up with a solution. Perhaps you'll be so kind as to go to Tread Tools, select Mark this thread as Solved.... rathe than expecting everyone else to do your job for you.