Quick sort problem

  • Thread starter Thread starter Suzanne
  • Start date Start date
S

Suzanne

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
 
You are sorting on column A not B

from
key1:=.Cells(2, 1),
to
key1:=.Cells(2, "B"),
 
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
 
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
 
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.
 
ok... how do I exclude empty rows?

Joel said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
ok... how do I exclude empty rows?

Joel said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
ok... how do I exclude empty rows?

Joel said:
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.



:

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
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sort Errors 4
sorting problem with range definition 2
Problem sorting a range of columns using VBA 1
Sorting left to right programatically 4
Sorting Problem 1
Sorting Macro Loop 2
sort doesn't sort numerically 2
sort macro stopped sorting 6

Back
Top