Can you check my code please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
I have a code on a worksheet which i would like to use on another sheet.
I changed the range etc to suit the new sheet but the sheet just ended up in a right mess when i run it.

The sheet i need the sort code on has this info for you.
Sheet name CLONING
Row 2 is headers.
Row 3 is hidden.
Values then start in row 3
The columns in use are D-I

The values in column D of which i need to sort are numbers & letters,low number to high number

So i used the code below where i changed the following
A5:Q to D3:I
Sheet name DATABASE to CLONING
=Range & .Range A6 to =Range & .Range to D4


Code:
Private Sub ChipSortSort_Click()    Dim x As Long
        Application.ScreenUpdating = False
        With Sheets("DATABASE")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range("A5:Q" & x).Sort key1:=Range("A6"), order1:=xlAscending, Header:=xlGuess
    End With
    ActiveWorkbook.Save
       Application.ScreenUpdating = True
    Sheets("DATABASE").Range("A6").Select
End Sub
 
Ok so I now understand X but I don't know why the page was all messed up.
You wouldn't think changing a letter or two would be that hard.

I will ill try you code tomorrow.
The filter arrow in the first cell sorts it no problem.
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
No it's column D.
The filter arrow in the first cell with a value in of column D.
 
Upvote 0
so back to my original question
What is the value of x ?
In post 10 Jeff told you what x is, and how/where its value is being calculate. Does the value make any sense ?

Instead of sorting the range, select it and see if that's the range you really want to be sorting.
Change the "A5:Q" to what you think it should be for this particular sheet.
Code:
        With Sheets("DATABASE")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range("A5:Q" & x).select    '.Sort key1:=Range("A6"), order1:=xlAscending, Header:=xlGuess
    End With
 
Last edited:
Upvote 0
Like everyone else it seems I am struggling to understand your request but maybe...

Code:
    With Sheets("DATABASE")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 4).End(xlUp).Row
            .Range("A5:Q" & x).Sort key1:=.Range("D6"), order1:=xlAscending, Header:=xlNo
    End With
 
Last edited:
Upvote 0
@ MARK858
I'm trying to lead the OP into discovering for himself that he has 3 blank, hidden columns at the start of his sheet,
and maybe along the way, a means of checking to see if he's giving Excel the actual range he wants to work with.
 
Upvote 0
Might be worth being more explicit based on the OP's signature :biggrin:
 
Upvote 0
Morning,

This code is on the sheet called DATABASE
Code:
Private Sub CustomerSort_Click()    Dim x As Long
        Application.ScreenUpdating = False
        With Sheets("DATABASE")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range("A5:Q" & x).Sort key1:=Range("A6"), order1:=xlAscending, Header:=xlGuess
    End With
    ActiveWorkbook.Save
       Application.ScreenUpdating = True
    Sheets("DATABASE").Range("A6").Select
End Sub

I also want to use it on a sheet called CLONING.
The range on the sheet for the columns in use are D to I
The first row with values is at row 4 and the last row with values at present is at 57

I am going to leave this as its to much of a conundrum now to even understand the replies.

Thanks
 
Upvote 0
Ian, you are not seeing the whole picture when looking at the CLONING sheet.
In the file you pm'd me a link to last week, columns A, B and C are blank and hidden.
You are determining the last row for the sort, the variable x, by looking for the last used row in column 1, which is column "A" and results in x having a value of 1.

At some point there has to be an understanding of the code you use, especially if you intend to reuse it.
As this will be the 24th time for you using the exact same
Code:
x = .Cells(.Rows.Count, 1).End(xlUp).Row
in similar situations throughout your sheet modules (I didn't look in the 19 user forms or 81 standard modules), I don't think it unreasonable to ask the value of x, and in post 14 present a means of you seeing what that value of x was doing in determining the range you would be trying to sort by having the code select the range instead of sorting it.

I'm now finished in this thread,
Good Luck with your project.
 
Last edited:
Upvote 0
Lets get this straight a minute please.

My brain doesnt tick correct.

You will never be a teacher for people with learning difficulties.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top