UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need Help Troubleshooting Macro, Office 2013    
 
   
bakersburg9
post Nov 9 2017, 03:54 PM
Post#1



Posts: 4,956
Joined: 2-November 04
From: Downey, CA


I have a very simple macro that selects multiple, non-contiguous columns that errs, and I can't figure out why: At the bottom, I have code that runs perfect - I'm stumped - can't see what's wrong ! pullhair.gif

CODE
Sub DailyPendingJobDeleteColumns()

'Delete columns to exclude
' 13 Columns R,S,X,Y,Z,AK,AL,AM,AN,AO,AP,AR,AS from 45 col of data - s/b 38 col left

Range("r1,s1,x1,y1,z1,ak1,al1,am1,an1,ao1,ap1,ar1,as1").EntireColumn.Select

   Selection.Delete Shift:=xlToLeft
  

End Sub


This DOES work:
CODE
Range("a1,b1,c1, d1,h1,j1,k1,l1,m1,n1,o1,p1,q1,r1,s1,t1,u1,v1,w1,x1,y1,z1,aa1,ab1,ac1,ad1,ae1,af1
,ag1,ah1,ai1,aj1").EntireColumn.Select

    Selection.Delete Shift:=xlToLeft
Go to the top of the page
 
doctor9
post Nov 9 2017, 04:00 PM
Post#2


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


bakersburg9,

First, I didn't get an error with your posted code. However, you should know it's not necessary to .Select a range if you're just going to delete it. Try this:

CODE
Range("r1,s1,x1,y1,z1,ak1,al1,am1,an1,ao1,ap1,ar1,as1").EntireColumn.Delete Shift:=xlToLeft


EDIT: Another alternative:
CODE
Range("R:S,X:Z,AK:AP,AR:AS").EntireColumn.Delete


Were you getting the error on the line that selects the columns or on the line that deletes the columns? What was the error?

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
bakersburg9
post Nov 9 2017, 05:53 PM
Post#3



Posts: 4,956
Joined: 2-November 04
From: Downey, CA


Dennis,
Thanks so much for your input - I stripped out the data, except for one row, and added a button to run the macro - it will blow up on the 2nd line of code - on my version, that first SELECTS the columns before deleting I get:
CODE
Run-time error ‘1004’
Delete method of Range class failed
I'm really, REALLY confused - this is a common type of code I use all the time, and it works great !

I was grasping at straws, and tried removing the filtering, but that wasn't it - I didn't think it was, but I was desperate - so grateful for your help, because I'm stumped ! confused.gif

Steve
Attached File(s)
Attached File  DeleteColumnTest.zip ( 20.02K )Number of downloads: 1
 
Go to the top of the page
 
doctor9
post Nov 9 2017, 05:57 PM
Post#4


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


Steve,

You can't delete the Entire Column because you're working with a Table within your worksheet.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
bakersburg9
post Nov 9 2017, 06:17 PM
Post#5



Posts: 4,956
Joined: 2-November 04
From: Downey, CA


QUOTE
can't delete the Entire Column because you're working with a Table within your worksheet
I'm surprised to hear that, because all the work I do with Excel is with tables - is there a way I can copy the data to a worksheet that's NOT a table - I want to, using the technical term here, "De-table" it (Stupid tables :-( )

EDIT: I have a simple macro on my QAT I use if I want to delete a column that I have any cell selected.... when I run my macro that blows up after selecting the columns, I run that macro from the button on the QAT, and it serves as a feasible work-around until I get this figured out - it deletes them just fine. I think this may be some quirky Microsoft voodoo....

This is the code attached to the button on my QAT:
CODE
ActiveCell.Columns("A:A").EntireColumn.Select
    ActiveCell.Activate
    Selection.Delete Shift:=xlToLeft
    ActiveCell.Offset(1, 0).Range("A1").Select
... it deletes all the columns, even on that workbook

Edit2: You're probably right about the table thing, it's just I can't figure out why I work within tables all the time, and these macros where my macro selects multiple, non-contiguous columns, then deletes them
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 10:15 PM