+ Reply to Thread
Results 1 to 3 of 3

VBA to delete empty columns that have Heading by other cells are Blank

  1. #1
    Registered User
    Join Date
    12-13-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    68

    Question VBA to delete empty columns that have Heading by other cells are Blank

    HI VBA Guru
    I have several large reports (over 200 columns in each) and all columns have headings. However 75% of the columns do not have any subsequent data i.e cells from Row 2 to the end of report are blanks. The blank columns are between other columns that have data and I want to retain them. I have searched on various sites but could not find anything suitable. I have come across the following code. I have very little knowledge of VBA.

    Since I will have to repeat this exercise several times I do not want to add codes to each file. What I want is to have an Excel template whereby to have 3 fields on worksheet called “MACRO_RunCode”. These are as follows:-
    FILE PATH: where user will insert full path of file - say in cell E14
    FILE NAME: where user will insert the name of the file - say in cell E16
    WORKSHEET NAME: where user will insert the name of the worksheet in each file - say in cell E18
    This worksheet will also contains the button that when clicked will run appropriate code to delete columns that have headings only and other cells are blanks.

    In addition I want to make this code useable for .xls OR xlsx files depending on what version of Excel one uses.

    Please can someone help modify this code:
    1. Sub Delete_Blanks()
    2. DIM myFile as String
    3. DIM myPath as String
    4. DIM myWorksheet as String
    5. DIM xlApp as String
    6. DIM xcelVersion as Variant
    7. ‘Open Excel Application
    8. Set xlApp = CreateObject (“Excel Application”)
    9. objExcel.Visible = True
    10. ‘Set Objects
    11. Set myPath = CreateObject .Range (“E14”) ‘Path as stated in cell E14
    12. Set myFile = ObjExcel.Workbooks.Open (“E16”) ‘File Name as stated in cell E16
    13. Set myWorksheet = Sheets("E18").Visible = True ‘Worksheet Name as stated in cell E18
    14.
    15. Sheets("Sheet1").Select
    16. Range("A1").Select
    17. LastRow = ActiveSheet.UsedRange.Row - 1 + _
    18. ActiveSheet.UsedRange.Rows.Count
    19. Application.ScreenUpdating = False
    20. For r = LastRow To 1 Step -1
    21. If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
    22. Next r
    23. LastColumn = ActiveSheet.UsedRange.Column - 1 + _
    24. ActiveSheet.UsedRange.Columns.Count
    25. Application.ScreenUpdating = False
    26. For c = LastColumn To 1 Step -1
    27. If Application.CountA(Columns(c)) = 0 Then Columns(c).Delete
    28. Next c

    29. End Sub

    I am attaching sample file

    Thanking you in advance
    Dhiresh
    Attached Files Attached Files
    Last edited by dhiresh; 07-22-2013 at 07:01 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: VBA to delete empty columns that have Heading by other cells are Blank

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

  3. #3
    Registered User
    Join Date
    12-13-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    68

    Re: VBA to delete empty columns that have Heading by other cells are Blank

    HI..
    I could not edit the above codes so here is the code again

    Please Login or Register  to view this content.
    Rgds
    Dhiresh

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Delete columns based upon a column heading?
    By Bandicoot in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-25-2013, 10:02 AM
  2. Delete Columns according to Heading
    By marshall84 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-26-2012, 07:28 AM
  3. [SOLVED] Help with a macro to delete empty (but not blank) cells OR sort blanks to the bottom
    By Cyali in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-07-2012, 09:04 PM
  4. [SOLVED] Delete rows with empty cells in columns B&C
    By Richard in forum Excel General
    Replies: 3
    Last Post: 03-17-2006, 08:20 PM
  5. Formula to delete blank cells across multiple columns?
    By SamFunMail in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1