ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Quick sort problem (https://www.excelbanter.com/excel-programming/405465-quick-sort-problem.html)

Suzanne
Quick sort problem
 
Not quite sure what's wrong with this sort...

Notes: To generate a merge document, data in columns F, G, and R are
merged; the sort is on COL B where this merged data exists; Col A does not
contain data; Col V is available for, but does not have data at this time

This:

Dim Rng As Range
Set Rng = .Range(.Range("V1"), .Cells(Rows.Count, "A").End(xlUp))

Rng.SORT key1:=.Cells(2, 1), Order1:=xlAscending, MatchCase:=False,
Header:=xlYes

Is resulting as:


W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Supplies/Materials Handling
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Supplies/Materials Handling
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Professional/Technical
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Professional/Technical
W3HVAA / 1422 / Professional/Technical
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Professional/Technical

joel
Quick sort problem
 
You are sorting on column A not B

from
key1:=.Cells(2, 1),
to
key1:=.Cells(2, "B"),

"Suzanne" wrote:

Not quite sure what's wrong with this sort...

Notes: To generate a merge document, data in columns F, G, and R are
merged; the sort is on COL B where this merged data exists; Col A does not
contain data; Col V is available for, but does not have data at this time

This:

Dim Rng As Range
Set Rng = .Range(.Range("V1"), .Cells(Rows.Count, "A").End(xlUp))

Rng.SORT key1:=.Cells(2, 1), Order1:=xlAscending, MatchCase:=False,
Header:=xlYes

Is resulting as:


W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Supplies/Materials Handling
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Supplies/Materials Handling
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Professional/Technical
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Professional/Technical
W3HVAA / 1422 / Professional/Technical
W3HVAA / 1422 / Administrative
W3HVAA / 1422 / Professional/Technical

Suzanne
Quick sort problem
 
Ok, changing the sort key did not help until I adjusted the range (from "A"
to "B")

Now... why are the 103 rows of data getting thrown down to row 83?


I'm executing "IHSFMerge" prior to executing "GetIHSFMerge" (see code below)

Sub GetIHSFMerge()

ActiveSheet.Unprotect

Sheets("IHSF DATA ENTRY").Range("B2:T500").Copy

With Sheets("MERGE DATA IHSF")
.Range("D2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
End With

Dim Lrow As Long
With ActiveSheet
Lrow = Range("D" & Rows.Count).End(xlUp).Row
Range("B2:C" & Lrow).FillDown
Range("AA2:AL" & Lrow).FillDown

Dim Rng As Range
Set Rng = .Range(.Range("V1"), .Cells(Rows.Count, "B").End(xlUp))

Rng.SORT key1:=.Cells(2, "B"), Order1:=xlAscending, MatchCase:=False,
Header:=xlYes

..Range("A13").Select

End With

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Sub DeleteIHSFMerge()
ActiveSheet.Unprotect
Range("B3:C500").Select
Selection.ClearContents
Range("D2:V500").Select
Selection.ClearContents
Range("A13").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Suzanne
Quick sort problem
 
Ok, changing the sort key did not help until I adjusted the range (from "A"
to "B")

Now... why are the 103 rows of data getting thrown down to row 83?


I'm executing [SORRY! Meant to say "DeleteIHSFMerge"] prior to executing
"GetIHSFMerge" (see code below)

Sub GetIHSFMerge()

ActiveSheet.Unprotect

Sheets("IHSF DATA ENTRY").Range("B2:T500").Copy

With Sheets("MERGE DATA IHSF")
.Range("D2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
End With

Dim Lrow As Long
With ActiveSheet
Lrow = Range("D" & Rows.Count).End(xlUp).Row
Range("B2:C" & Lrow).FillDown
Range("AA2:AL" & Lrow).FillDown

Dim Rng As Range
Set Rng = .Range(.Range("V1"), .Cells(Rows.Count, "B").End(xlUp))

Rng.SORT key1:=.Cells(2, "B"), Order1:=xlAscending, MatchCase:=False,
Header:=xlYes

..Range("A13").Select

End With

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Sub DeleteIHSFMerge()
ActiveSheet.Unprotect
Range("B3:C500").Select
Selection.ClearContents
Range("D2:V500").Select
Selection.ClearContents
Range("A13").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

joel
Quick sort problem
 
The blank cells in column B are being put at the top of the sort area.
Sometimes you think cells are blank but are not.



"Suzanne" wrote:

Ok, changing the sort key did not help until I adjusted the range (from "A"
to "B")

Now... why are the 103 rows of data getting thrown down to row 83?


I'm executing [SORRY! Meant to say "DeleteIHSFMerge"] prior to executing
"GetIHSFMerge" (see code below)

Sub GetIHSFMerge()

ActiveSheet.Unprotect

Sheets("IHSF DATA ENTRY").Range("B2:T500").Copy

With Sheets("MERGE DATA IHSF")
.Range("D2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
End With

Dim Lrow As Long
With ActiveSheet
Lrow = Range("D" & Rows.Count).End(xlUp).Row
Range("B2:C" & Lrow).FillDown
Range("AA2:AL" & Lrow).FillDown

Dim Rng As Range
Set Rng = .Range(.Range("V1"), .Cells(Rows.Count, "B").End(xlUp))

Rng.SORT key1:=.Cells(2, "B"), Order1:=xlAscending, MatchCase:=False,
Header:=xlYes

.Range("A13").Select

End With

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Sub DeleteIHSFMerge()
ActiveSheet.Unprotect
Range("B3:C500").Select
Selection.ClearContents
Range("D2:V500").Select
Selection.ClearContents
Range("A13").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Suzanne
Quick sort problem
 
ok... how do I exclude empty rows?

"Joel" wrote:

The blank cells in column B are being put at the top of the sort area.
Sometimes you think cells are blank but are not.



"Suzanne" wrote:

Ok, changing the sort key did not help until I adjusted the range (from "A"
to "B")

Now... why are the 103 rows of data getting thrown down to row 83?


I'm executing [SORRY! Meant to say "DeleteIHSFMerge"] prior to executing
"GetIHSFMerge" (see code below)

Sub GetIHSFMerge()

ActiveSheet.Unprotect

Sheets("IHSF DATA ENTRY").Range("B2:T500").Copy

With Sheets("MERGE DATA IHSF")
.Range("D2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
End With

Dim Lrow As Long
With ActiveSheet
Lrow = Range("D" & Rows.Count).End(xlUp).Row
Range("B2:C" & Lrow).FillDown
Range("AA2:AL" & Lrow).FillDown

Dim Rng As Range
Set Rng = .Range(.Range("V1"), .Cells(Rows.Count, "B").End(xlUp))

Rng.SORT key1:=.Cells(2, "B"), Order1:=xlAscending, MatchCase:=False,
Header:=xlYes

.Range("A13").Select

End With

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Sub DeleteIHSFMerge()
ActiveSheet.Unprotect
Range("B3:C500").Select
Selection.ClearContents
Range("D2:V500").Select
Selection.ClearContents
Range("A13").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

joel
Quick sort problem
 
The solution may be different depending on these reason for the empty rows.
If the empty rows are at the end of the rows of data then only copy the cells
that have data.

First try changing this statement

from
Sheets("IHSF DATA ENTRY").Range("B2:T500").Copy
to
with Sheets("IHSF DATA ENTRY")
LastRow = .Range("B" & rows.count).end(xlup).row
.Range("B2:T" & LastRow).Copy
end with

If the blank rows are in the midle of the orginal data then you have to
delete the empty rows after you sort

FirstRow = Range("A1").End(xlDown).Row
LastRow = FirstRow - 1
Rows("1:" & LastRow).Delete

The sometimes the rows appear to be empty but are not. then I go back to
the original worksheet and delete all empty rows and columns.

Start by going to cell A1 and press Cntl-Shift Down-Arror to get to the last
row. Hightlight the 1st empty row and then Cntrl-Shift Down-Arro. the is
should highlight all the empty rows. Then delete these rows.

Repeat process for columns. Now rerun the macro.

"Suzanne" wrote:

ok... how do I exclude empty rows?

"Joel" wrote:

The blank cells in column B are being put at the top of the sort area.
Sometimes you think cells are blank but are not.



"Suzanne" wrote:

Ok, changing the sort key did not help until I adjusted the range (from "A"
to "B")

Now... why are the 103 rows of data getting thrown down to row 83?


I'm executing [SORRY! Meant to say "DeleteIHSFMerge"] prior to executing
"GetIHSFMerge" (see code below)

Sub GetIHSFMerge()

ActiveSheet.Unprotect

Sheets("IHSF DATA ENTRY").Range("B2:T500").Copy

With Sheets("MERGE DATA IHSF")
.Range("D2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
End With

Dim Lrow As Long
With ActiveSheet
Lrow = Range("D" & Rows.Count).End(xlUp).Row
Range("B2:C" & Lrow).FillDown
Range("AA2:AL" & Lrow).FillDown

Dim Rng As Range
Set Rng = .Range(.Range("V1"), .Cells(Rows.Count, "B").End(xlUp))

Rng.SORT key1:=.Cells(2, "B"), Order1:=xlAscending, MatchCase:=False,
Header:=xlYes

.Range("A13").Select

End With

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Sub DeleteIHSFMerge()
ActiveSheet.Unprotect
Range("B3:C500").Select
Selection.ClearContents
Range("D2:V500").Select
Selection.ClearContents
Range("A13").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Suzanne
Quick sort problem
 
Thanks very much! With your notes below, I was able to take a targeted look
at the entire code; as usual, this problem was due to my own general lack of
coding skills (I originally had 'Skip Blanks = False' when copying over data
from the source worksheet). The following now seems to be working well.

SuzLeigh

ActiveSheet.Unprotect
Sheets("IHSF DATA ENTRY").Range("B2:T500").Copy

With Sheets("MERGE DATA IHSF")
.Range("D2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=True, Transpose:=False
End With

Dim Rng As Range
Set Rng = .Range(.Range("V1"), .Cells(Rows.Count, "B").End(xlUp))
Rng.SORT Key1:=.Cells(2, "B"), Order1:=xlAscending, MatchCase:=False,
Header:=xlYes
..Range("A13").Select

End With


"Joel" wrote:

The solution may be different depending on these reason for the empty rows.
If the empty rows are at the end of the rows of data then only copy the cells
that have data.

First try changing this statement

from
Sheets("IHSF DATA ENTRY").Range("B2:T500").Copy
to
with Sheets("IHSF DATA ENTRY")
LastRow = .Range("B" & rows.count).end(xlup).row
.Range("B2:T" & LastRow).Copy
end with

If the blank rows are in the midle of the orginal data then you have to
delete the empty rows after you sort

FirstRow = Range("A1").End(xlDown).Row
LastRow = FirstRow - 1
Rows("1:" & LastRow).Delete

The sometimes the rows appear to be empty but are not. then I go back to
the original worksheet and delete all empty rows and columns.

Start by going to cell A1 and press Cntl-Shift Down-Arror to get to the last
row. Hightlight the 1st empty row and then Cntrl-Shift Down-Arro. the is
should highlight all the empty rows. Then delete these rows.

Repeat process for columns. Now rerun the macro.

"Suzanne" wrote:

ok... how do I exclude empty rows?

"Joel" wrote:

The blank cells in column B are being put at the top of the sort area.
Sometimes you think cells are blank but are not.



"Suzanne" wrote:

Ok, changing the sort key did not help until I adjusted the range (from "A"
to "B")

Now... why are the 103 rows of data getting thrown down to row 83?


I'm executing [SORRY! Meant to say "DeleteIHSFMerge"] prior to executing
"GetIHSFMerge" (see code below)

Sub GetIHSFMerge()

ActiveSheet.Unprotect

Sheets("IHSF DATA ENTRY").Range("B2:T500").Copy

With Sheets("MERGE DATA IHSF")
.Range("D2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
End With

Dim Lrow As Long
With ActiveSheet
Lrow = Range("D" & Rows.Count).End(xlUp).Row
Range("B2:C" & Lrow).FillDown
Range("AA2:AL" & Lrow).FillDown

Dim Rng As Range
Set Rng = .Range(.Range("V1"), .Cells(Rows.Count, "B").End(xlUp))

Rng.SORT key1:=.Cells(2, "B"), Order1:=xlAscending, MatchCase:=False,
Header:=xlYes

.Range("A13").Select

End With

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Sub DeleteIHSFMerge()
ActiveSheet.Unprotect
Range("B3:C500").Select
Selection.ClearContents
Range("D2:V500").Select
Selection.ClearContents
Range("A13").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub


All times are GMT +1. The time now is 07:38 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com