Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reduce excel file size by deleting blank rows and columns?? | Excel Discussion (Misc queries) | |||
Deleting blank cells w/o impacting other rows/columns | Excel Programming | |||
Newbee - deleting blank columns | Excel Programming | |||
OsCommerce - Easy Populate Script - CSV/TXT Conversion Problem. | Excel Discussion (Misc queries) | |||
deleting text is easy... but what about this?!!! | Excel Programming |