Need help deleting empty columns not cells

[zone709]

Hi I need help deleting Columns that have empty data in whole column. Not if there is empty cells. example if I select Column C D and E because all the way down that line has no data. Then I want to delete them so it shifts to the left.

I tried Find & Select Co to special then blanks and delete to try to record a code, but it doesn't do what I need it to do.

 

[Joe4]

Try this:

Sub MyDeleteColumns()

    Dim Cols As Variant
    Dim i As Long
    Dim mycol As String
    Dim myRow As Long
    
'   List columns to check, in reverse order
    Cols = Array("E", "D", "C")

'   Loop through each column
    For i = LBound(Cols) To UBound(Cols)
'       Capture column letter
        mycol = Cols(i)
'       Check value in first row of current column
        If Cells(1, mycol) = "" Then
'           Go down, and see if it at last row and it is empty
            myRow = Cells(1, mycol).End(xlDown).Row
            If myRow = Rows.Count And Cells(myRow, mycol) = "" Then
                Columns(mycol).Delete
            End If
        End If
    Next i
            
End Sub

 

[zone709]

ok gonna try this in my code now. Will let you know thank you

 

[jamcall]

Something like this might work for you:

Sub DelBlankCol()
    myCol = "G"
    lr = Cells(Rows.Count, myCol).End(xlUp).Row
    If lr = 1 And IsEmpty(Range(myCol & lr).Value) Then
        Range(myCol & ":" & myCol).EntireColumn.Delete
    End If
    
End Sub

You'd have to tailor it to select the columns that you are interested in...

HTH,
~ Jim

 

[Joe4]

Here is a little shorter version of the first code I posted:

Sub MyDeleteColumns()

    Dim Cols As Variant
    Dim i As Long
    Dim myCol As String

'   List columns to check, in reverse order
    Cols = Array("E", "D", "C")

'   Loop through each column
    For i = LBound(Cols) To UBound(Cols)
'       Capture column letter
        myCol = Cols(i)
        If Columns(myCol).SpecialCells(xlCellTypeBlanks).Cells.Count = Rows.Count Then
            Columns(myCol).Delete
        End If
    Next i
    
End Sub

 

[zone709]

Joe I was giving an example on those columns. E D C. I'm trying for the code to locate the empty rows lets say if it was E D & C and then delete those empty rows that have absolutely no data thanks

 

[Joe4]

I am sorry, but based on what you originally said here:

Hi I need help deleting Columns that have empty data in whole column. Not if there is empty cells. example if I select Column C D and E because all the way down that line has no data. Then I want to delete them so it shifts to the left.

and then this:

I'm trying for the code to locate the empty rows lets say if it was E D & C and then delete those empty rows that have absolutely no data thanks

It is very confusing and unclear to me what you are asking.

Perhaps if you could paste a data sample, and then walk us through what should happen it that example, it will be clearer.

 

[zone709]

Ok see if this helps. Sorry for any confusion. As you see in D & B there is no data so I need to delete those rows.


Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​
1​	john
2​	mike
3​
4​			JJ		Jay
5​
6​
7​
8​
9​
10​					Jay
11​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

 

[Joe4]

I think part of the problem is you are mixing nomenclature here. B and D are columns, not rows. So I am not sure if you want to delete columns or rows.
So what exactly do you want to delete in that scenario?
Maybe post what your expected result of this example should look like.

 

[zone709]

Yeah I meant columns sorry not rows. Its Friday thinking to fast and typing. Not thinking right.

Results:

Excel 2016 (Windows) 32 bit

	A​	B​	C​
1​	John
2​	mike
3​
4​		JJ	Jay
5​
6​
7​
8​
9​
10​			Jay
11​
12​
13​

Sheet: Sheet1