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
> Sort Data In Excel Without Hard Coding, Office 2013    
 
   
bakersburg9
post Nov 20 2017, 12:14 PM
Post#1



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


This is driving me nuts - I'm trying to write a macro to sort data by column4 (COL D), and when I use the macro recorder, whether with Rel Ref on or off, there's all this hard-coding, like the actual name of the worksheet - I just want Excel to sort the data in the current worksheet - even the examples I found on the 'net had absolute references to cells/names

I know (I think I know) PART of my code is:

CODE
   Range(Selection, Selection.End(xlToRight)).Select


or:
CODE
  Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
any help would be greatly appreciated...
Go to the top of the page
 
larai
post Nov 20 2017, 03:30 PM
Post#2



Posts: 1,064
Joined: 8-February 02
From: California, USA


Hello,

CODE
Sub TestSort()
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=ws.[d1], _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange ws.[a1].CurrentRegion
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub


Change .Header = xlYes
if your data has column headers
Go to the top of the page
 
bakersburg9
post Nov 20 2017, 04:35 PM
Post#3



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


Larai,

Thank you, my fellow Californian !!!! Worked awesome ! I've been spinning my wheels all day - can't find anything like this - I'm ashamed this isn't already in my tool bag - I can't understand why there's so much with hard-coding absolute references, but it is what it is ...

thanks.gif

really appreciate your help....

Steve
Go to the top of the page
 
larai
post Nov 21 2017, 04:40 PM
Post#4



Posts: 1,064
Joined: 8-February 02
From: California, USA


Hi Bakersburg9,

Glad it worked for you. The reason there's so much hard coding with absolute references (and explict selects) is because of the macro recorder. It's a great tool, but as you continue to work with excel's objects, you'll learn how to use different methods of using the objects and then it's just a matter of cleaning up the selects and using object references instead.
Go to the top of the page
 
dflak
post Nov 22 2017, 12:19 PM
Post#5


Utter Access VIP
Posts: 6,017
Joined: 22-June 04
From: North Carolina


Generally speaking when the recorder says:
CODE
Thing.Select
Selection.Action


You can replace it with
CODE
Thing.Action


And if you can make "Thing" flexible, then you have more powerful code.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
doctor9
post Nov 22 2017, 03:42 PM
Post#6


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


Just chiming in with my two cents...

Whenever you see the Macro Recorder "Select"ing something, it can almost always be removed.

From my copy of John Walkenbach's "Excel For Windows 95 Power Programming With VBA 2nd Edition":

QUOTE
Although the macro recorder generally selects cells before doing anything with them, selecting is not necessary and may actually slow down your macro. Recording a very simple copy-and-paste operation generates four lines of VBA code (two of which use the Select method). Here's an example:
CODE
Range("A1").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste

These four lines can be replaced with a single statement, such as the following:
CODE
Range("A1").Copy(Range("B1"))

Notice that this statement does not use the Select method.


This was so important to me that I marked the page so I could come back to it later. It's a really simple example, but it taught me an important lesson in cleaning up and optimizing code.

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
 
dflak
post Nov 22 2017, 04:32 PM
Post#7


Utter Access VIP
Posts: 6,017
Joined: 22-June 04
From: North Carolina


Oh well, since we are all piling on ... smile.gif.

The other thing the macro recorder does sometimes is to give you too much information. For example, if you recorded a macro to change the center header on a sheet, you'll get dozens of lines of code. Most of these reset what you already have to their current values. You can pretty much tell what line you really need, in this case it will have .centerheader.

I consider myself no novice at VB coding in Excel, but I still rely on the macro recorder to give me the syntax of how to get to things, in particular things like the various parts of a chart.

And while we are on the subject. In the VB Editor go to Tools - > Options.

Click On Require Variable Declaration. This puts the words Option Explicit on the top of every module. What Option Explicit does is forces you to declare all variables (using the Dim statement). This way if you mistype the name of the variable, the compiler will pick it up for you. Just do it! It will make your programming life easier from here on out.

You might also want to click off Auto Syntax Check. If you have a syntax error, the line will be highlighted in red. You can't miss it. With this box checked on, you get a dialog box that you have to clear. Sometimes I am typing some code and I want to copy and paste an expression from another line. With the box clicked on, I leave the line, get the error dialog box, cancel it, then go and copy and paste my code snippet. It' annoying.
Attached File(s)
Attached File  Option_Explicit.JPG ( 34.67K )Number of downloads: 1
 

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
JonSmith
post Nov 22 2017, 06:26 PM
Post#8



Posts: 3,161
Joined: 19-October 10



It is also worth venturing off the macro recorder too, using tables and structured references and such it wont generate much relating to the Listobject which is what you really want.

I personally only use the macro recorder to remind me of a syntax but I rarely use the code it makes. You'll understand it waaaay better when you write yourself.
A good place to start is google, loads of examples of whatever you are trying to do.

Dennis suggests reading a book which depending on how you learn can be great. I have never read a coding book and am an advanced VBA coder but I just learn in a different way. Find the method that works for you.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 11:30 PM