[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Excel VBA Delete Columns on Multiple Sheets Using MS Access 2010

Posted on 2014-09-07
10
Medium Priority
?
1,297 Views
Last Modified: 2014-09-08
I need to delete multiple columns on all Excel worksheets using MS Access VBA. Thanks
0
Comment
Question by:shieldsco
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 1

Expert Comment

by:Subramani N
ID: 40308571
Include the below code in the vba,

With Sheets(sheet_name)
     .Columns("A:AN").EntireColumn.Delete
End With

Or

With Sheets(sheet_name)
     Range("A:D,F:J,M:O,S:V").Delete
End With
0
 
LVL 36

Expert Comment

by:Norie
ID: 40308572
Which columns on which sheets?
0
 
LVL 2

Expert Comment

by:Computer Egghead
ID: 40308573
This should do the trick.  Run it as many times as you wish.  Remember that once you delete a column, the columns will shift, that is, if you delete column D, what was column E becomes D.
Single column example, just enter D
Multiple column example, enter D:F
Public Sub Macro1()
    Dim columnRange As String
    columnRange = InputBox("Column(s) to delete? (D:F)")
    DeleteColumnsAllWorksheets columnRange
End Sub

Private Sub DeleteColumnsAllWorksheets(columnRange As String)
    Dim w As Worksheet
    For Each w In ThisWorkbook.Worksheets
        w.Range(columnRange).EntireColumn.Delete Shift:=xlToLeft
    Next w
    Set w = Nothing
End Sub

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 46

Expert Comment

by:aikimark
ID: 40308597
@shieldsco

Do you want the columns physically deleted from the worksheet(s) or do you want to clear the cell data in those columns?
0
 
LVL 2

Expert Comment

by:Computer Egghead
ID: 40308661
Just realized you asked for MS Access 2010 (If you are in Access, it is Access VBA, not Excel VBA), so I'm amending my answer.  

1. Add a reference to Microsoft Office 14.0 Object Library and Microsoft Excel 14.0 Object Library

2. Add this to a module in your Access app:

Option Explicit
Option Compare Database

Public Sub ProcessExcelFile()
    Dim o As Object
    Set o = Application.FileDialog(msoFileDialogFilePicker)
    o.AllowMultiSelect = False
    o.Title = "Select an Excel workbook"
    o.Filters.Clear
    o.Filters.Add "Excel workbooks", "*.xl*"
    If o.Show Then
        Dim sPath As String
        sPath = o.SelectedItems(1)
    End If
    Dim columnRange As String
    columnRange = InputBox("Column(s) to delete? (Example, D:F)")
    DeleteColumnsAllWorksheets sPath, columnRange
End Sub

Private Function DeleteColumnsAllWorksheets(filePath As String, columnRange As String) As Boolean
    Dim bResult As Boolean: bResult = True
    Dim oApp As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oWS As Excel.Worksheet

    On Error GoTo ErrorHandler
    Set oApp = New Excel.Application
    Set oWB = oApp.Workbooks.Open(filePath)
    For Each oWS In oWB.Worksheets
        oWS.Range(columnRange).EntireColumn.Delete Shift:=xlToLeft
    Next oWS
    oWB.Close True
    Set oWS = Nothing
    Set oWB = Nothing
    Set oApp = Nothing
    GoTo EndProcedure
    
ErrorHandler:
    bResult = False
EndProcedure:
    DeleteColumnsAllWorksheets = bResult
End Function

Open in new window

0
 

Author Comment

by:shieldsco
ID: 40310119
ComputerEgghead - how do I access my spreadsheet located on a network drive (the spreadsheet name will always be the same)?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40310291
@shieldsco

Please answer my question
0
 

Author Comment

by:shieldsco
ID: 40310645
aikimark - I want the columns physically deleted from the worksheet(s)
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 40310690
The VBA code will look something like this
Dim oXL as object
dim wkb as object
dim wks as object
set oxl=createobject("excel.application")
set wkb=oxl.workbooks.open("\\servername\path to workbook\workbookname.xlsx")
wkb.worksheets("sheetname").range("C:D").Delete
wkb.close true
oxl.quit
set oxl=nothing

Open in new window

0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question