easy script for deleting blank columns
 

Hi, I liked Ron De Bruin's script for deleting blank rows. It worked. I have
tried to also modified it to delete blank columns. This is what I have:
Dim Lcol As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim Startcol As Long
Dim Endcol As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
Startcol = 1
Endcol = 25

For Lcol = Endcol To Startcol Step -1

If Application.CountA(.cols(Lcol)) = 0 Then .cols(Lcol).Delete
'This will delete the column if the whole column is empty (all
rows)

Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

Not knowing lots about visual basic, I have been finding and modifying
script without a deep understanding of the various areas which is obviously
why this isn't working for me. I have a problem with "If
Application.CountA(.cols(Lcol)) = 0 Then .cols(Lcol).Delete" line but I guess
I might have other issues out there I am yet to discover.

Am I on the right track here? Can anyone help with this??
Cheers
Gai

easy script for deleting blank columns
 

You were close:

If Application.CountA(.Columns(Lcol)) = 0 Then .Columns(Lcol).Delete

You abbreviated .columns with .cols. That was a deal breaker!

After that change, the code worked ok for me.

GaiGauci wrote:

Hi, I liked Ron De Bruin's script for deleting blank rows. It worked. I have
tried to also modified it to delete blank columns. This is what I have:
Dim Lcol As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim Startcol As Long
Dim Endcol As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
Startcol = 1
Endcol = 25

For Lcol = Endcol To Startcol Step -1

If Application.CountA(.cols(Lcol)) = 0 Then .cols(Lcol).Delete
'This will delete the column if the whole column is empty (all
rows)

Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

Not knowing lots about visual basic, I have been finding and modifying
script without a deep understanding of the various areas which is obviously
why this isn't working for me. I have a problem with "If
Application.CountA(.cols(Lcol)) = 0 Then .cols(Lcol).Delete" line but I guess
I might have other issues out there I am yet to discover.

Am I on the right track here? Can anyone help with this??
Cheers
Gai

--

Dave Peterson

easy script for deleting blank columns
 

Hi,


use .columns(lcol) and not .cols(lcol)

Sam

"GaiGauci" wrote:

Hi, I liked Ron De Bruin's script for deleting blank rows. It worked. I have
tried to also modified it to delete blank columns. This is what I have:
Dim Lcol As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim Startcol As Long
Dim Endcol As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
Startcol = 1
Endcol = 25

For Lcol = Endcol To Startcol Step -1

If Application.CountA(.cols(Lcol)) = 0 Then .cols(Lcol).Delete
'This will delete the column if the whole column is empty (all
rows)

Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

Not knowing lots about visual basic, I have been finding and modifying
script without a deep understanding of the various areas which is obviously
why this isn't working for me. I have a problem with "If
Application.CountA(.cols(Lcol)) = 0 Then .cols(Lcol).Delete" line but I guess
I might have other issues out there I am yet to discover.

Am I on the right track here? Can anyone help with this??
Cheers
Gai

easy script for deleting blank columns
 

Thanks to both Sam and David. Just learning script and I think I make it up
as I go!

Thanks again for your input.

Cheers
Gai

"GaiGauci" wrote:

Hi, I liked Ron De Bruin's script for deleting blank rows. It worked. I have
tried to also modified it to delete blank columns. This is what I have:
Dim Lcol As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim Startcol As Long
Dim Endcol As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
Startcol = 1
Endcol = 25

For Lcol = Endcol To Startcol Step -1

If Application.CountA(.cols(Lcol)) = 0 Then .cols(Lcol).Delete
'This will delete the column if the whole column is empty (all
rows)

Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

Not knowing lots about visual basic, I have been finding and modifying
script without a deep understanding of the various areas which is obviously
why this isn't working for me. I have a problem with "If
Application.CountA(.cols(Lcol)) = 0 Then .cols(Lcol).Delete" line but I guess
I might have other issues out there I am yet to discover.

Am I on the right track here? Can anyone help with this??
Cheers
Gai