Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Macro records STATIC info when DYNAMIC is required.... 2

Status
Not open for further replies.
alchohaz

alchohaz

Technical User
Mar 25, 2004
216
GB
I am new to all of the VB Code stuff, although i am getting to grips with it slowly, i am also going blind trying to understand it all !!

My issue is as follows:

I have recorded a Macro that will start at Cell C2, from there i keyed CNT+SHFT+DOWN ARROW to select all of the rows available (the reason i have used this is that the amount of rows will change from sheet to sheet, but i would like to use the same Macro)..

Anyway when i run this macro it always applied the same values of that recorded in the code DESPITE using the CNT+SHFT+DOWN ARROW to select the required rows.

The problem in the code appears to be here:

Code: 
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E2:E15") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C15") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
Where ranges E2:E15 & C2:C15 are defined, i still want to sort in this column but do not want it stop at 15...

Full code below - NB i have tried running this by selecting the required data and then naming the range in Excel, but this still fails !!! Grrr PLEASE HELP :)

Code: 
Sub Demo_RCD()
'
' Demo_RCD Macro
'

'
    Range("C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E2:E15") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C15") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:J15")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub
 
Sort by date Sort by votes


Hi,

Untested, but give this a try...
Code: 
Sub Demo_RCD()
'
' Demo_RCD Macro
'

'
    Dim ws As Worksheet
    
    Set ws = ActiveWorkbook.Worksheets("Sheet1")
    
    With ws.Range("A1").Sort.SortFields
        .Clear
        .Add _
            Key:=ws.Range("E1"), _
            SortOn:=xlSortOnValues, _
            Order:=xlDescending, _
            DataOption:=xlSortNormal
        .Add _
            Key:=Range("C1"), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortTextAsNumbers
    End With
    With ws.Sort
        .SetRange Range("A1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote


Oh, BTW, this assumes that your table in A1 is 1) contiguous and 2) isolated from all other data by at least one empty row & column.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote


and how sloppy of me..[blush]
Code: 
    Dim ws As Worksheet
    
    Set ws = ActiveWorkbook.Worksheets("Sheet1")
    
    With ws.Range("A1").Sort.SortFields
        .Clear
        .Add _
            Key:=ws.Range("E1"), _
            SortOn:=xlSortOnValues, _
            Order:=xlDescending, _
            DataOption:=xlSortNormal
        .Add _
            Key:=[b]ws.[/b]Range("C1"), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortTextAsNumbers
    End With
    With ws.Sort
        .SetRange [b]ws.[/b]Range("A1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote


Thank you - i have changed A1 to C1 (A1 has some blanks, C1 does NOT) -

I did paste this inot a new module, but unfortunatley received the following:

"run time error"
"Unable to get the Sort Property of the Range Class
 
Upvote 0 Downvote


Code: 
    With ws.Sort.SortFields
I'm still getting accustomed to 2007.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
What about this ?
Code: 
Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Sheet1")
With ws.Sort
  With .SortFields
    .Clear
    .Add _
        Key:=ws.Range("E1"), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortNormal
    .Add _
        Key:=ws.Range("C1"), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortTextAsNumbers
  End With
  .SetRange ws.Range("C1").CurrentRegion
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
That's Great - seesm to be working, thank you very much... Will test it on a few sheets now :)

To be honest i am struggling to interpret this could you give me a couple of pointers? - I can see that the "With ws.Sort.SortFields" appears to define the columns to be sorted and how - but where is the range being selected?

Thanks again
 
Upvote 0 Downvote


The With...End With structure is a simplification. Look it up in VB Help.

The range reference is ...
Code: 
  .SetRange ws.Range("C1").CurrentRegion
.SetRange refers to the With above.

the CurrentRegion Property, returns a range object that is contiguous with it's reference, which is ws.Range("C1").

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote

I am trying to repeat this with the "RemoveDuplicates" but struggling - any tips?

i.e. once the data is sorted i want to removce the duplicate values from column 3 (C)


 
Upvote 0 Downvote

once the data is sorted i want to removce the duplicate values from column 3 (C)
Click to expand...
This does not make sense. Whatever is in column 3 is part of your table. You cannot remove duplicate values in one column of a table without destroying the entire table.

You can, however, remove duplicates rows from a table or a range that includes more than one column, by specifying the entire range.

HINT: Try to do what you are trying to code, on the sheet, using the remove duplicates feature.

Please post your code, if you have further questions.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I will try to make it clearer, the data i have has duplicate values in Column 3 of the table - it has duplicates in other columns too, but only the ones in column 3 are an issue for this pieces of work as this is the "unique field" that i am using.

Exmaple: in the case below, 1 x Tony and 1 x Paul would be removed (removing the Dups from column C)
Code: 
A	    B	    C

Tony	Blue	  27	
Adam	Red	   34	
Paul	Blue  	27	
John	Blue	  18	
Tony	Red	   27

Here is the code i have recorded, but i was trying to immitate the style you have used above and this DOES NOT match that.

Code: 
Sub RemDup()
'
' RemDup Macro
'

'
    Range("C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Range("$A$1:$J$15").RemoveDuplicates Columns:=3, Header:=xlYes
End Sub

The problem i am going to face again here is that "ActiveSheet.Range("$A$1:$J$15")" is absolute, but for my work this will vary from sheet to sheet

thanks




 
Upvote 0 Downvote
ActiveSheet.Range("C1").CurrentRegion.RemoveDuplicates Columns:=3, Header:=xlYes

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote


Yes, you can that remove duplicates in column C, but you will ALSO loose data in the remainder of your table.

Are you aware of that and does that matter?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote

SkipVought - If you are referring to the ROW that is removed, yes i am aware and this is what i am after.

PHV's Solution appears to work, but if you have another slant on it, i am interested..

Thank you both for your help so far anyway :)

 
Upvote 0 Downvote



Your entire table has changed. You have lost data.

You no longer have Paul or Tony Red.

Is the data in columns A & B IRRELEVANT?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote

SkipVought - Yes that is correct, what you have written is spot on, this is what i am seeking to do - i do not want dupliactes in Column C regarless of the impact they have elsewhere..

If you could see the acutual data it would be clearer to you, but alas i cannot post that !!!



 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
1K
dhookom
dhookom
morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
1K
morechocolate
morechocolate
remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
1K
strongm
strongm
remeng
  • Locked
  • Question
Getting results from a dynamic dimension array
Replies
2
Views
722
Andrzejek
Andrzejek
remeng
  • Locked
  • Question
Delete dynamic table rows without removing password protection
Replies
7
Views
653
remeng
remeng

Part and Inventory Search

Sponsor

Back
Top