Log In
Register
Home
New Member
March 12, 2020
Offline
```
COLS : NO | | B | C | | D
01 : 1 | | 8 | 3 | | 2
02 : | | | 4 | |
03 : | | | | |
04 : 2 | | 5 | 2 | | 6
```
How can I shift the values to the left and delete empty rows but remain the values in it's own column? Goal is:
```
COLS : NO | B | C | D
01 : 1 | 8 | 3 | 2
02 : | | 4 |
03 : 2 | 5 | 2 | 6
04 : | | |
```
Number 4 in C2 should remain in it's own column (It's an extra value for record number 1 at column C).
What I get now is not what I want:
```
COLS : NO | B | C | D
01 : 1 | 8 | 3 | 2
02 : 4 | | |
03 : 2 | 5 | 2 | 6
04 : | | |
```
Trusted Members
December 20, 2019
Offline
you want to delete blank columns and blank rows?
It is much easier if you attach an example workbook to save recreating the data
The below should work, but there is probably an easier way with Power Query
Sub DeleteCols()
Dim cCol As Long
Dim cCount As Long
Dim rRow As Long
Dim rCount As Long
For cCol = 1 To 100
'Columns(cCol).Select
cCount = WorksheetFunction.CountA(Columns(cCol))
If cCount = 0 Then
Columns(cCol).Delete
End If
'Debug.Print cCount
Next cCol
For rRow = 1 To 100
'Rows(rRow).Select
rCount = WorksheetFunction.CountA(Rows(rRow))
If rCount = 0 Then
Rows(rRow).Delete
End If
'Debug.Print rCount
Next rRow
Range("a1").Activate
End Sub
1 Guest(s)
