?
Solved

Delete entire column using named range

Posted on 2008-06-12
1
Medium Priority
?
516 Views
Last Modified: 2010-08-05
I'm missing a basic concept here on assigning ranges. What I am trying to do is delete a column that I have pre-defined as a named range.
I have attached a code snippet.
I am getting an error on line:
        ActiveSheet.Columns(rng).EntireColumn.Delete

The range "rptColSubBatch" is a valid range.
I originally defined the range as comprising the entire column E (i.e. "$E:$E")
The error generated was: Runtime Error '13'  Type Mismatch
I changed the range to comprise only the first cell in the column (i.e. $E$1)
The error generated for that range was: Runtime Error '1004'  Application-defined or Object-defined  error

Because I am working with numerous columns that may change, I need to stick with named ranges.

What am I doing wrong?

Shambalad





Public Sub DeleteColumns()
   Dim strRange As String
   Dim rng As Range
   
   strRange = "rptColSubBatch"
   Set rng = Range(strRange)
   ActiveSheet.Columns(rng).EntireColumn.Delete
End Sub

Open in new window

0
Comment
Question by:shambalad
1 Comment
 
LVL 7

Accepted Solution

by:
shambalad earned 0 total points
ID: 21773355
Figured it out.
Public Sub DeleteColumns()
   Dim strRange As String
   strRange = "rptColSubBatch"
   ActiveSheet.Columns.Range(strRange).EntireColumn.Delete
end Sub

Open in new window

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

830 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