Macro - Data Sort -Variable Selection

Hi all!!!

I am trying to get my macro to run a sort in a non specific cell for column 
D.....there are many steps up to this point, I will include the last couple 
in the code...here is what I have so far....

    Columns("C:C").Find(What:="x").EntireRow.Insert
    Columns("C:C").Find(What:="x").EntireRow.Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Sort Key1:=Range("D63"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Instead of the range being specific for  D63, I want it to select the D 
column after if finds X to the end of the wkst.  Any ideas on how I can 
accomplish this???

Thanks in advance......

    
0
6/3/2005 4:28:10 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
384 Views

Similar Articles

[PageSpeed] 13

..Sort is pretty forgiving.  Didn't it work using D63?

I've used:

....key1:=range("d:d"), ...

And that works, too.

Frantic Excel-er wrote:
> 
> Hi all!!!
> 
> I am trying to get my macro to run a sort in a non specific cell for column
> D.....there are many steps up to this point, I will include the last couple
> in the code...here is what I have so far....
> 
>     Columns("C:C").Find(What:="x").EntireRow.Insert
>     Columns("C:C").Find(What:="x").EntireRow.Select
>     Range(Selection, Selection.End(xlDown)).Select
>     Range(Selection, Selection.End(xlToRight)).Select
>     Selection.Sort Key1:=Range("D63"), Order1:=xlAscending, Header:=xlGuess, _
>         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> 
> Instead of the range being specific for  D63, I want it to select the D
> column after if finds X to the end of the wkst.  Any ideas on how I can
> accomplish this???
> 
> Thanks in advance......
> 
> 

-- 

Dave Peterson
0
ec357201 (5290)
6/3/2005 9:18:07 PM
Selecting "d:d" works, but it will select the whole column... I only want to 
sort the selection after I find x and sort that....

"Dave Peterson" wrote:

> ..Sort is pretty forgiving.  Didn't it work using D63?
> 
> I've used:
> 
> ....key1:=range("d:d"), ...
> 
> And that works, too.
> 
> Frantic Excel-er wrote:
> > 
> > Hi all!!!
> > 
> > I am trying to get my macro to run a sort in a non specific cell for column
> > D.....there are many steps up to this point, I will include the last couple
> > in the code...here is what I have so far....
> > 
> >     Columns("C:C").Find(What:="x").EntireRow.Insert
> >     Columns("C:C").Find(What:="x").EntireRow.Select
> >     Range(Selection, Selection.End(xlDown)).Select
> >     Range(Selection, Selection.End(xlToRight)).Select
> >     Selection.Sort Key1:=Range("D63"), Order1:=xlAscending, Header:=xlGuess, _
> >         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> > 
> > Instead of the range being specific for  D63, I want it to select the D
> > column after if finds X to the end of the wkst.  Any ideas on how I can
> > accomplish this???
> > 
> > Thanks in advance......
> > 
> > 
> 
> -- 
> 
> Dave Peterson
> 
0
6/6/2005 8:03:28 PM
Don't select column D.

But do use it in the key1:=range("d:d") portion.

Then what happens?

Frantic Excel-er wrote:
> 
> Selecting "d:d" works, but it will select the whole column... I only want to
> sort the selection after I find x and sort that....
> 
> "Dave Peterson" wrote:
> 
> > ..Sort is pretty forgiving.  Didn't it work using D63?
> >
> > I've used:
> >
> > ....key1:=range("d:d"), ...
> >
> > And that works, too.
> >
> > Frantic Excel-er wrote:
> > >
> > > Hi all!!!
> > >
> > > I am trying to get my macro to run a sort in a non specific cell for column
> > > D.....there are many steps up to this point, I will include the last couple
> > > in the code...here is what I have so far....
> > >
> > >     Columns("C:C").Find(What:="x").EntireRow.Insert
> > >     Columns("C:C").Find(What:="x").EntireRow.Select
> > >     Range(Selection, Selection.End(xlDown)).Select
> > >     Range(Selection, Selection.End(xlToRight)).Select
> > >     Selection.Sort Key1:=Range("D63"), Order1:=xlAscending, Header:=xlGuess, _
> > >         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> > >
> > > Instead of the range being specific for  D63, I want it to select the D
> > > column after if finds X to the end of the wkst.  Any ideas on how I can
> > > accomplish this???
> > >
> > > Thanks in advance......
> > >
> > >
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
ec357201 (5290)
6/6/2005 9:33:05 PM
Reply:

Similar Artilces:

Macros and reminder screen or message about test company.
I would like to add a macro and run it from the task manager. So far this part is cool. The problem Im having is with pop up windows in GP. For example "reminder" screen automatically pops up when a users logs in. This seems to stop the macro. Are there any suggestion around this screen besides turning off reminder screen from popping up? I did find the article on microsoft referring to using Logging file "macro.log'. This option got me pass the login prompts but this option didnt work either for pops. Thanks for any help, Mar ...

Macro to delete an entire row with cells missing a specific chartacter #2
Hey Harald, This is the script I am using to remove "IS" Sub Findanddelete_IS() Dim rng As Range Dim what As String what = "IS" Do Set rng = ActiveSheet.UsedRange.Find(what) If rng Is Nothing Then Exit Do Else Rows(rng.Row).Delete End If Loop End Sub So...my programming experience is a tiny bit limited, not sure how t delete anything missing the IS as opposed to containing the IS. understand in theory but my knowledge of syntax is the limiter. Harald Staff Wrote: > Hi Asf > > This is far more useful if you figure it out yourself, since you'r > pr...

Data and Other Records
Hi - I'm building a pivot table consisting of counts of a list of items. I want to include only the first 50 items in detail, but lump all the others into an "other" category with a count on it, instead of detail on records above 50. Hopefully this makes some sense! Thanks, Greg. -- groggrog ------------------------------------------------------------------------ groggrog's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27400 View this thread: http://www.excelforum.com/showthread.php?threadid=469155 ...

Sorting within Groups
In my new spreadsheet I have a list of employees in 192 departments. The sheet is sorted by department, division, then employee name. I have a blank line between groups--the blank line is for percentage calculations. I also have due dates (you may have seen my earlier post). Let's say that I've managed to color code the due dates (way overdue, just or soon to be overdue, and fine). I would like to sort them with by color but keep them within the departments and not move the blank lines. When I try this, all the blank lines are sort together. Is there anyway, to...

Making data span 2 columns
Hi, I'm new to this community and new to the RMS system. I am trying to modify the PrintTransactionDetails sub in the receipt.xml to be 2 columns and put the entry.description information onto another row. The person who writes the descriptions likes to be wordy and I need the entire 40 columns in order to hold the text. I changed the 3 columns into 2 with a format of 71% for the item number or description and 29% for the sales amount. The description and a null field occupy the first row and the item number and amount occupies the second row. I have the transaction d...

How can i change chart data point format based on value?
I have a simple column chart with a single data series. I would like to change the color of an individual data point column depending on value. Thanks very much. You want to make a conditional chart: http://peltiertech.com/Excel/Charts/ConditionalChart1.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Harold96" <Harold96@discussions.microsoft.com> wrote in message news:3674450D-70A9-4227-B29A-36262B7BE723@microsoft.com... >I have a simple column chart with a single data series. I would...

geographic map data
I would like to find a simple set of geographic map data (Latitude and Longitude) in EXCEL tables for drawing country outline maps for use in EXCEL Charts ...

Calculate total number off cells with data in
Hi All Hope I find you well I have a spreadsheet that has a number of cells that may or may not have data in them depending on curtain criteria. What I would like to be able to do is count the number of cells with data in (data is alphanumerical) and display the total number of cells with data in in a different cell. Any ideas? Hi Gazza, > What I would like to be able to do is count the number of cells with data in (data is alphanumerical) and display the total number of cells with data in in a different cell. Any ideas? > Check out the COUNTA worksheet function. Regards, Jan Kare...

MAcro Problem (probably easy)
I'm trying to record and run a macro that copies the format, colors an a graphic from another excel report. I start the macro recorder, the open the workbook, copy, switch workbooks, paste all, then switc workbooks, close the graphic source workbook, and stop the recorder. When I run the macro, it gives me an error like it can't open the othe workbook. Any tips -- bludovic ----------------------------------------------------------------------- bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480 View this thread: http://www.excelforum.com/sho...

Open form based on selection
I have a form [frm_modelselect] where I will select a chair model. Then I want to hit a command, or just have a macro that runs onChange. Either way, I want to open a specific form based on the Model that I selected in [frm_modelselect]. How do I go about doing this? Thanks in advance. Travis On Mon, 30 Apr 2007 14:24:04 -0700, Tdahlman wrote: > I have a form [frm_modelselect] where I will select a chair model. Then I > want to hit a command, or just have a macro that runs onChange. > Either way, I want to open a specific form based on the Model that I > selected in [frm_...

Determine if data is trending UP or DOWN
All I posted this question in another area with little activity. Please review my question and reply with any suggestions http://groups.google.com/group/microsoft.public.excel.worksheetfunctinos/browse_thread/thread/51433fe1f0242504/f908dfbcb5ae9487?hl=en#f908dfbcb5ae9487 Ignore all points except the first and the last. If the last point is greater than the first point, the trend is up. -- Gary''s Student - gsnu200719 "Steve" wrote: > All I posted this question in another area with little activity. > Please review my question and reply with any suggestions > ...

changing columns in the select names dialog box
Outlook 2003 using Microsoft Exchange Server. 1) Is it possible to change which columns display and in what order in the "select columns" dialog box? 2) I have found that I can remove a column by dragging and sliding a column to the left against the next column on the left. Unfortunately I have removed some columns that I would like to see. How do I get the columns to appear again? Many thanks. Jeff 1) No.=20 2) That's a tricky one! It took a couple of minutes for me to figure = out. Position your mouse pointer exactly over the divider on the left = side of the colu...

Copy filtered data (Values only)
The following code copies filtered data: Set Rng = ActiveSheet.AutoFilter.Range Rng.Copy Destination:=Worksheets("Sheet2").Range("A1") but copies values and formulae. (e.g. .PasteSpecial xlPasteValues) How do I modify to the copy only the values Thanks! Set Rng = ActiveSheet.AutoFilter.Range Rng.Copy Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Steve" <Steve@discussions.microsoft.com> wrote in message news:5E54D33F-2...

How to convert an excel macro code to a Visual basic program
As it is in the subjec title, this is my question: How to convert an Excel macro code to a Visual Basic program? The fact is I am using some excel macros written for excel 2002 and I want to work with them just like a distinct program. I tried to do it directly, but I faced with lots of errors. Any help would be highly appreciated. ...

How to retrieve real data type of function's parameters
Hi all, I have a function which accepts some parameters. When I call this function, I used some casting operation and In this function I want to get the real data type name of these parameters. Can I do it? If I can, how should I do? Please help me. Thanks. If you are looking for something like .NET reflection, you won't find an identical equivalent. But look up "Run-Time Type Information" and "Accessing Run-Time Class Information" on MSDN. -- Regards, Nish [VC++ MVP] http://www.voidnish.com http://blog.voidnish.com "Nguyen Van Binh" <binhnv@...

Sorting Problems
I am trying to sort numerically a column with a mix of numbers and text: 1 2 3 (36 hrs) 4 5 (19 hrs) 12 13 (6 hrs) but this is what I end up with: 1 2 4 12 13 (6 hrs) 3 (36 hrs) 5 (19 hrs) Any help? If you split the data into two columns--one column for the number portion and one column for the parenthetical portion, you can sort by that first column. In fact, you don't even need that second column! One way to do that is to insert a column to the right of the original column. Then select that column Data|Text to columns delimited by spa...

using a popup to make percenatges on selected cells
Hi I'm hoping someone can help with some code. I'm selecting a group of cells containing numbers. I want via a popup to change the selected numbers to the percentage entered in the popup. For example - I select my cells and run the macro. The popup asks for a number and I enter '50'. All the selected numbers then change to 50 percent of their previous value. It would apply equally to a selected whole column of numbers. Grateful for any help. Best Wishes hi Sub changenumber() Dim n As Double n = InputBox("enter a number") n = n / 100 ...

data input format
Hi I have a user entry form and need to ensure the correct data format is used when entering. At the moment it is a text box on a VBA data entry form, I want to code the text box to only accept dd/mm/yy format. -- thanks Roy If you get the dat as a string, then you can perform very specific tests on parts of that string. For example: Sub StrictFormat() Dim s As String, i As Integer s = Application.InputBox(prompt:="Date?", Type:=2) If Len(s) <> 8 Then MsgBox "Bad Format" Exit Sub End If ary = Split(s, "/") If UBound...

color coding different data series
Hello! I have about 8 spreadsheets of data that I need to compile into one chart. However, I need to be able to look at the chart and see which spreadsheet a data point is from. Is there a quick way to color code a group of data points from one spreadsheet? I know I could change the color individually, but with over 50 data points, I'd rather not if I don't have to! Thank you. Hi, If I undserstand you correctly all you need to do is right click on one of the data points and select Format Data Series. You can then set the colour, line weight, pattern etc. as you want. Repeat f...

Excel Macro Problem, Add-in need to work in every workbook & Error:9 Subscript out of range
I will first write the code then explain the problem ThisWorkbook Part: Private Sub Workbook_BeforeClose(Cancel As Boolean) If PropExis = True And PropValue = True Then UnStampEveryPage End If End Sub Private Sub Workbook_BeforePrint(Cancel As Boolean) If PropExis = True And PropValue = True Then UnStampEveryPage StampEveryPage End If End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If PropExis = True And PropValue = True Then UnStampEveryPage End If End Sub Module Part: Option Explicit 'Adds Word Art to Sheet Sub AddWordArt(i As Long) Dim celTo...

Macro confusion
I'm trying to make macro keys that specify a department in the item search and get ready for a name in the search, so I start the macro (ctrl-f3) but it won't let me end the recording until I close the open search window, what am I doing wrong? I tried pausing the recording and closing the windows to no avail! -Jerry It seems like you didn't quite finish the process. You need to ctrl-f3 to start the process, open your window, ctrl-f3 to pause your process, close your window, and then ctrl-f3 one more time to end the macro. The closing of the window will not be recorded. -...

Data Validation #20
I have 4 cells all beside each other in my Excel Spreadsheet. They are all data validations that are linked to one another. I.E. each depends on the input from the previous cell. I'm looking for a code that will clear the cells (but keep the data validations) if I change one of the previous cells. For Example: Cell 1 contains these items: Shovel, Axe, and a Saw. If I choose Shovel in the first cell then my options for the second cell are now Flat or Round. Then the third box will give me the price depending on which I choose. However the problem is that once I pick the Flat and t...

Auto Select Range?
Hey all, I am trying to get my macro to select all data (and perform calculations) if that data is included in a range of G/L numbers. I remember seeing something similar to this on this site, but I can't seem to find it again. Basically, I have G/L #s from 701000 to 999999. 701000 to 703104 are loan accounts. I want the macro to choose all of the accounts between and including 701000 and 703104. That way, if accounting adds G/L 702101, I won't have to change the macro. Thanks in advance for the help. How about a nice formula where a1 and a2 contain your numbers or use t...

Transfer OLE Object data to table
hi, i have a Microsoft Office Spreadsheet 11.0 in my form, how do i transfer the data i have key into it to a table that i have created earlier, i will be only using 3 columns in the OLE object, my table have 3 columns as well.(now i have a form with the OLE object, a save button to save the OLE object to the table, a table) Should i make a connection to the OLE object or just retrive straight from the object? Should i retrieve the data cell by cell or using a range "A1:Cn", n= EOF? can someone point me to a good link on my topic? thanks KT -- Message posted via http://www....

Running Word Macros
Is it possible to include in an Excel macro a direction to open a Wor Document and run a Word Macro there? Thanks Matthe -- Message posted from http://www.ExcelForum.com ...