I am having trouble retaining blank rows in a worksheet upon sorting,
I have the sort code as follows:

Sub SortJS()
WS.Activate
Selection.Sort Key1:=Range("G2"), Order1:=xlAscending,
Key2:=Range("E2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:= _
xlSortNormal

End Sub

As normal any blank rows are wiped out, I can't find a good way to
prevent this from happening so I need to know if there is an "Column
Index" function in Excel similar to MS Access where an Index column is
automatically numbered and corrected for any new or deleted entries.
If this is possible I can use this column to sort all of the data and
keep it hidden.

Thanks,

Chad

Blank rows are being sorted to the bottom of the sort range. Because there
is no data you are not seeing this effect. Put numbers into columns that are
not part in the sort keys. You will then see what is really happening.
" wrote:

I am having trouble retaining blank rows in a worksheet upon sorting,
I have the sort code as follows:

Sub SortJS()
WS.Activate
Selection.Sort Key1:=Range("G2"), Order1:=xlAscending,
Key2:=Range("E2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:= _
xlSortNormal

End Sub

As normal any blank rows are wiped out, I can't find a good way to
prevent this from happening so I need to know if there is an "Column
Index" function in Excel similar to MS Access where an Index column is
automatically numbered and corrected for any new or deleted entries.
If this is possible I can use this column to sort all of the data and
keep it hidden.

Thanks,

Chad

On Feb 17, 11:09 am, Joel wrote:
Blank rows are being sorted to the bottom of the sort range. Because there
is no data you are not seeing this effect. Put numbers into columns that are
not part in the sort keys. You will then see what is really happening.

Thanks Joel,

Is there a way to AutoNum a column as you do in Access?

Chad

Edit=Fill=Series. . .

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
On Feb 17, 11:09 am, Joel wrote:
Blank rows are being sorted to the bottom of the sort range. Because
there
is no data you are not seeing this effect. Put numbers into columns that
are
not part in the sort keys. You will then see what is really happening.

Thanks Joel,

Is there a way to AutoNum a column as you do in Access?

Chad

If you enter in the first few rows the sequence of numbers. It could be 1 2
3 or even 5 10 15. Then select the sequece with the mouse. You will see a
black square on the lower right corner of the selected cells. Use the mouse
and pull it down the black square to rest of the worksheet and the numbers
will automatically be placed in the cells.

" wrote:

On Feb 17, 11:09 am, Joel wrote:
Blank rows are being sorted to the bottom of the sort range. Because there
is no data you are not seeing this effect. Put numbers into columns that are
not part in the sort keys. You will then see what is really happening.

Thanks Joel,

Is there a way to AutoNum a column as you do in Access?

Chad

On Feb 17, 11:54 am, Joel wrote:
If you enter in the first few rows the sequence of numbers. It could be 1 2
3 or even 5 10 15. Then select the sequece with the mouse. You will see a
black square on the lower right corner of the selected cells. Use the mouse
and pull it down the black square to rest of the worksheet and the numbers
will automatically be placed in the cells.

One big problem, my worksheet entries and blank rows are not in a
predictable order. I might have 3 entries under one job number and 75
under another, then a blank row in-between different job numbers. I
couldn't find a way to fill because the blank rows do not get assigned
series numbers.

I hate to do this but since I've got Tom's attention I'll ask; I'm am
also have trouble with the code below. It is copying a value from cell
E5 "18002LT" and pasting it to another worksheet, JobNum is dimmed
public as variant and all cells for copy and paste are General format.
It is returning error '1004' PasteSpecial method of Range class
failed, I have tried dimming JobNum as string, setting all cells in
question as Text, and tried all PasteSpecial methods. What am I not
getting set correctly?

If Range("E5").Value < "" Then
Range("E5").Copy 'Copying text from pull down list.
Col = ("E") 'variable to set column on next sheet
JobNumb = Sheet5.Range("E5").Value 'job number format
~"18000LT"
Insert_1_row 'sub to go to worksheet and insert blank row
Selection.PasteSpecial (xlPasteValues)
End If

Thanks,

Chad

On Feb 17, 12:23 pm, wrote:
On Feb 17, 11:54 am, Joel wrote:

If you enter in the first few rows the sequence of numbers. It could be 1 2
3 or even 5 10 15. Then select the sequece with the mouse. You will see a
black square on the lower right corner of the selected cells. Use the mouse
and pull it down the black square to rest of the worksheet and the numbers
will automatically be placed in the cells.

Sorry, but I figured out what is causing the Copy/Paste problem but I
don't know why. I have code in WorkSheet_Activate to sort the sheet,
this is runs before inserting a row and pasting. Please let me know if
I'm doing something wrong in this code or you just cannot sort or
manipulate the sheet before a paste.

'Private Sub Worksheet_Activate()
'Dim rng As Range
'Dim rng2 As Range
'
'Set rng = Range("G2")
'Set rng2 = Range("E2")
'rng.Sort Key1:=rng(1), Order1:=xlAscending, header:=xlYes,
Key2:=rng2(1), Order2:=xlAscending, header:=xlYes
'End Sub

Thanks

The focus may be changing. When you copy the code the focus may be on one
worksheet. Then the sort is changing the focus to another worksheet. The
past is going to where the last focus ends up. You probably just have to
activate the correct worksheet before you do the paste

" wrote:

On Feb 17, 12:23 pm, wrote:
On Feb 17, 11:54 am, Joel wrote:

If you enter in the first few rows the sequence of numbers. It could be 1 2
3 or even 5 10 15. Then select the sequece with the mouse. You will see a
black square on the lower right corner of the selected cells. Use the mouse
and pull it down the black square to rest of the worksheet and the numbers
will automatically be placed in the cells.

Sorry, but I figured out what is causing the Copy/Paste problem but I
don't know why. I have code in WorkSheet_Activate to sort the sheet,
this is runs before inserting a row and pasting. Please let me know if
I'm doing something wrong in this code or you just cannot sort or
manipulate the sheet before a paste.

'Private Sub Worksheet_Activate()
'Dim rng As Range
'Dim rng2 As Range
'
'Set rng = Range("G2")
'Set rng2 = Range("E2")
'rng.Sort Key1:=rng(1), Order1:=xlAscending, header:=xlYes,
Key2:=rng2(1), Order2:=xlAscending, header:=xlYes
'End Sub

Thanks

Good possibility that when you insert your row, you clear the clipboard - so
when you try to paste you get an error.

Assume the sheet with the data has the code name sheet1

Dim Col as String
Dim JobNumb as String



With Sheet1.Range("E5")
if .Value < "" then
Col = "E" 'variable to set column on next sheet
JobNumb = Sheet5.Range("E5").Value 'job number format ~"18000LT"
Insert_1_row 'sub to go to worksheet and insert blank row
.copy
selection.PasteSpecial xlpasteValues
' or selection.Value = Value
End if
End with

or if Sheet5 is the source sheet

With Sheet5.Range("E5")
if .Value < "" then
Col = "E" 'variable to set column on next sheet
JobNumb = .Range("E5").Value 'job number format ~"18000LT"
Insert_1_row 'sub to go to worksheet and insert blank row
.copy
selection.PasteSpecial xlpasteValues
' or selection.Value = Value (skip the copy
' or selection = JobNumb (skip the copy
end if
End with

It isn't totally clear to me what Sheet5 is - the source sheet or the
destination sheet and of course, I don't know what happens in Insert_1_row,
but assume that result of that sub is that the destination cell is selected.

The main point is that you will enjoy the most success if you copy and paste
in one command or for paste special, in successive commands.

When pasting values for single cells you can do

destinationCell = sourceCell and skip the copy and paste

Adjust to fit.

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
On Feb 17, 11:54 am, Joel wrote:
If you enter in the first few rows the sequence of numbers. It could be
1 2
3 or even 5 10 15. Then select the sequece with the mouse. You will see
a
black square on the lower right corner of the selected cells. Use the
mouse
and pull it down the black square to rest of the worksheet and the
numbers
will automatically be placed in the cells.

One big problem, my worksheet entries and blank rows are not in a
predictable order. I might have 3 entries under one job number and 75
under another, then a blank row in-between different job numbers. I
couldn't find a way to fill because the blank rows do not get assigned
series numbers.

I hate to do this but since I've got Tom's attention I'll ask; I'm am
also have trouble with the code below. It is copying a value from cell
E5 "18002LT" and pasting it to another worksheet, JobNum is dimmed
public as variant and all cells for copy and paste are General format.
It is returning error '1004' PasteSpecial method of Range class
failed, I have tried dimming JobNum as string, setting all cells in
question as Text, and tried all PasteSpecial methods. What am I not
getting set correctly?

If Range("E5").Value < "" Then
Range("E5").Copy 'Copying text from pull down list.
Col = ("E") 'variable to set column on next sheet
JobNumb = Sheet5.Range("E5").Value 'job number format
~"18000LT"
Insert_1_row 'sub to go to worksheet and insert blank row
Selection.PasteSpecial (xlPasteValues)
End If

Thanks,

Chad

On Feb 17, 12:47 pm, Joel wrote:
The focus may be changing. When you copy the code the focus may be on one
worksheet. Then the sort is changing the focus to another worksheet. The
past is going to where the last focus ends up. You probably just have to
activate the correct worksheet before you do the paste

Works great, thanks. Just had to copy after activating and inserting a
row on the other worksheet.

I continue to fight this sorting problem, seems like everyone that
wants to sort a sheet with blank rows is in the same boat. So how
about this; can I find only the blank rows and number them? I would
have to do this from the top down and number the blanks sequentially,
then when it reaches the end of the sheet data stop when it sees 2
blank rows. I received great advice from Tom on how to search from the
bottom up and insert new rows with data and a blank row in-between, so
if I can get the sort down I should be set.

Thanks,

Chad

I have done in the past

do while (cell(row + 1,column).value = "") and cell(row + 2,column).value =
"")

" wrote:

On Feb 17, 12:47 pm, Joel wrote:
The focus may be changing. When you copy the code the focus may be on one
worksheet. Then the sort is changing the focus to another worksheet. The
past is going to where the last focus ends up. You probably just have to
activate the correct worksheet before you do the paste

Works great, thanks. Just had to copy after activating and inserting a
row on the other worksheet.

I continue to fight this sorting problem, seems like everyone that
wants to sort a sheet with blank rows is in the same boat. So how
about this; can I find only the blank rows and number them? I would
have to do this from the top down and number the blanks sequentially,
then when it reaches the end of the sheet data stop when it sees 2
blank rows. I received great advice from Tom on how to search from the
bottom up and insert new rows with data and a blank row in-between, so
if I can get the sort down I should be set.

Thanks,

Chad