I have created a sheet called WORK ORDER where all of my product options are
listed. I made it so that if an option was not picked, the cell that
contained that option would be blank. I then wrote a code in VBE that would
delete all blank cells in WORK ORDER. What I would like to have happen now
is have the new condensed list reach a certain point (A63) and the remaining
portion of that list move over to F14 and work its way down to cell F57 if
needed. If the list continues after that point I would need the remainder to
start on J1.

I am having fits with this for some reason, so if anyone has a similar code
or knows how to go about this, it would be greatly appreciated.

After you run your code to condense lines then do a count of the lines

With ActiveCell
i = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
End With

calculate how many breaks you need and move the records using a case
structure.

Hope this helps

Maybe I could see the code you are using already?

"Cerberus" wrote:

> I have created a sheet called WORK ORDER where all of my product options are
> listed. I made it so that if an option was not picked, the cell that
> contained that option would be blank. I then wrote a code in VBE that would
> delete all blank cells in WORK ORDER. What I would like to have happen now
> is have the new condensed list reach a certain point (A63) and the remaining
> portion of that list move over to F14 and work its way down to cell F57 if
> needed. If the list continues after that point I would need the remainder to
> start on J1.
>
> I am having fits with this for some reason, so if anyone has a similar code
> or knows how to go about this, it would be greatly appreciated.
>
>

Thank you for replying. I have not had time to try your idea yet, I had to
take time off to move (fun times). Here is the code I have used to remove
blank spaces:

Private Sub Worksheet_Activate()

Dim HiddenRow&, RowRange As Range

'*****************************
'< Set the 1st & last rows to be hidden >
Const FirstRow As Long = 1
Const LastRow As Long = 2000

'< Set the column that contains data >
Const DataCol As String = "A"

'*****************************

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

For HiddenRow = FirstRow To LastRow

'Take the Length of the value in column A of each row
If Len(Range(DataCol & HiddenRow).Value) <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub


"JR Form" wrote:

> After you run your code to condense lines then do a count of the lines
>
> With ActiveCell
> i = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
> End With
>
> calculate how many breaks you need and move the records using a case
> structure.
>
> Hope this helps
>
> Maybe I could see the code you are using already?
>
> "Cerberus" wrote:
>
> > I have created a sheet called WORK ORDER where all of my product options are
> > listed. I made it so that if an option was not picked, the cell that
> > contained that option would be blank. I then wrote a code in VBE that would
> > delete all blank cells in WORK ORDER. What I would like to have happen now
> > is have the new condensed list reach a certain point (A63) and the remaining
> > portion of that list move over to F14 and work its way down to cell F57 if
> > needed. If the list continues after that point I would need the remainder to
> > start on J1.
> >
> > I am having fits with this for some reason, so if anyone has a similar code
> > or knows how to go about this, it would be greatly appreciated.
> >
> >

Hi Cerberus,

You are hiding the rows of information. This could cause a problem when you
try pasteing information to adjacen columns. Is there a reason you are not
deleting the row?
If you want wriete a line to return to the top of the range you are
evaluating for the line count then run the code sinpet I gae you.
You may want to write a function to break the line counts into copy segments
to move them. Do you need help with this?


"Cerberus" wrote:

> Thank you for replying. I have not had time to try your idea yet, I had to
> take time off to move (fun times). Here is the code I have used to remove
> blank spaces:
>
> Private Sub Worksheet_Activate()
>
> Dim HiddenRow&, RowRange As Range
>
> '*****************************
> '< Set the 1st & last rows to be hidden >
> Const FirstRow As Long = 1
> Const LastRow As Long = 2000
>
> '< Set the column that contains data >
> Const DataCol As String = "A"
>
> '*****************************
>
> ActiveWindow.DisplayZeros = False
> Application.ScreenUpdating = False
>
> For HiddenRow = FirstRow To LastRow
>
> 'Take the Length of the value in column A of each row
> If Len(Range(DataCol & HiddenRow).Value) <> 0 Then
> 'there's something in this row - don't hide
> Rows(HiddenRow).EntireRow.Hidden = False
> Else
> 'there's nothing in this row yet - hide it
> Rows(HiddenRow).EntireRow.Hidden = True
> End If
>
> Next HiddenRow
>
> Application.ScreenUpdating = True
>
> End Sub
>
>
> "JR Form" wrote:
>
> > After you run your code to condense lines then do a count of the lines
> >
> > With ActiveCell
> > i = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
> > End With
> >
> > calculate how many breaks you need and move the records using a case
> > structure.
> >
> > Hope this helps
> >
> > Maybe I could see the code you are using already?
> >
> > "Cerberus" wrote:
> >
> > > I have created a sheet called WORK ORDER where all of my product options are
> > > listed. I made it so that if an option was not picked, the cell that
> > > contained that option would be blank. I then wrote a code in VBE that would
> > > delete all blank cells in WORK ORDER. What I would like to have happen now
> > > is have the new condensed list reach a certain point (A63) and the remaining
> > > portion of that list move over to F14 and work its way down to cell F57 if
> > > needed. If the list continues after that point I would need the remainder to
> > > start on J1.
> > >
> > > I am having fits with this for some reason, so if anyone has a similar code
> > > or knows how to go about this, it would be greatly appreciated.
> > >
> > >

I am very new at writting VBA codes so any help would be greatly appreciated.
I have found that hidding the lines has caused issues like you said it would


Thanks for all your help on all of this.

"JR Form" wrote:

> Hi Cerberus,
>
> You are hiding the rows of information. This could cause a problem when you
> try pasteing information to adjacen columns. Is there a reason you are not
> deleting the row?
> If you want wriete a line to return to the top of the range you are
> evaluating for the line count then run the code sinpet I gae you.
> You may want to write a function to break the line counts into copy segments
> to move them. Do you need help with this?
>
>
> "Cerberus" wrote:
>
> > Thank you for replying. I have not had time to try your idea yet, I had to
> > take time off to move (fun times). Here is the code I have used to remove
> > blank spaces:
> >
> > Private Sub Worksheet_Activate()
> >
> > Dim HiddenRow&, RowRange As Range
> >
> > '*****************************
> > '< Set the 1st & last rows to be hidden >
> > Const FirstRow As Long = 1
> > Const LastRow As Long = 2000
> >
> > '< Set the column that contains data >
> > Const DataCol As String = "A"
> >
> > '*****************************
> >
> > ActiveWindow.DisplayZeros = False
> > Application.ScreenUpdating = False
> >
> > For HiddenRow = FirstRow To LastRow
> >
> > 'Take the Length of the value in column A of each row
> > If Len(Range(DataCol & HiddenRow).Value) <> 0 Then
> > 'there's something in this row - don't hide
> > Rows(HiddenRow).EntireRow.Hidden = False
> > Else
> > 'there's nothing in this row yet - hide it
> > Rows(HiddenRow).EntireRow.Hidden = True
> > End If
> >
> > Next HiddenRow
> >
> > Application.ScreenUpdating = True
> >
> > End Sub
> >
> >
> > "JR Form" wrote:
> >
> > > After you run your code to condense lines then do a count of the lines
> > >
> > > With ActiveCell
> > > i = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
> > > End With
> > >
> > > calculate how many breaks you need and move the records using a case
> > > structure.
> > >
> > > Hope this helps
> > >
> > > Maybe I could see the code you are using already?
> > >
> > > "Cerberus" wrote:
> > >
> > > > I have created a sheet called WORK ORDER where all of my product options are
> > > > listed. I made it so that if an option was not picked, the cell that
> > > > contained that option would be blank. I then wrote a code in VBE that would
> > > > delete all blank cells in WORK ORDER. What I would like to have happen now
> > > > is have the new condensed list reach a certain point (A63) and the remaining
> > > > portion of that list move over to F14 and work its way down to cell F57 if
> > > > needed. If the list continues after that point I would need the remainder to
> > > > start on J1.
> > > >
> > > > I am having fits with this for some reason, so if anyone has a similar code
> > > > or knows how to go about this, it would be greatly appreciated.
> > > >
> > > >

I will see what I can do for you.

"Cerberus" wrote:

> I am very new at writting VBA codes so any help would be greatly appreciated.
> I have found that hidding the lines has caused issues like you said it would
>
>
> Thanks for all your help on all of this.
>
> "JR Form" wrote:
>
> > Hi Cerberus,
> >
> > You are hiding the rows of information. This could cause a problem when you
> > try pasteing information to adjacen columns. Is there a reason you are not
> > deleting the row?
> > If you want wriete a line to return to the top of the range you are
> > evaluating for the line count then run the code sinpet I gae you.
> > You may want to write a function to break the line counts into copy segments
> > to move them. Do you need help with this?
> >
> >
> > "Cerberus" wrote:
> >
> > > Thank you for replying. I have not had time to try your idea yet, I had to
> > > take time off to move (fun times). Here is the code I have used to remove
> > > blank spaces:
> > >
> > > Private Sub Worksheet_Activate()
> > >
> > > Dim HiddenRow&, RowRange As Range
> > >
> > > '*****************************
> > > '< Set the 1st & last rows to be hidden >
> > > Const FirstRow As Long = 1
> > > Const LastRow As Long = 2000
> > >
> > > '< Set the column that contains data >
> > > Const DataCol As String = "A"
> > >
> > > '*****************************
> > >
> > > ActiveWindow.DisplayZeros = False
> > > Application.ScreenUpdating = False
> > >
> > > For HiddenRow = FirstRow To LastRow
> > >
> > > 'Take the Length of the value in column A of each row
> > > If Len(Range(DataCol & HiddenRow).Value) <> 0 Then
> > > 'there's something in this row - don't hide
> > > Rows(HiddenRow).EntireRow.Hidden = False
> > > Else
> > > 'there's nothing in this row yet - hide it
> > > Rows(HiddenRow).EntireRow.Hidden = True
> > > End If
> > >
> > > Next HiddenRow
> > >
> > > Application.ScreenUpdating = True
> > >
> > > End Sub
> > >
> > >
> > > "JR Form" wrote:
> > >
> > > > After you run your code to condense lines then do a count of the lines
> > > >
> > > > With ActiveCell
> > > > i = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
> > > > End With
> > > >
> > > > calculate how many breaks you need and move the records using a case
> > > > structure.
> > > >
> > > > Hope this helps
> > > >
> > > > Maybe I could see the code you are using already?
> > > >
> > > > "Cerberus" wrote:
> > > >
> > > > > I have created a sheet called WORK ORDER where all of my product options are
> > > > > listed. I made it so that if an option was not picked, the cell that
> > > > > contained that option would be blank. I then wrote a code in VBE that would
> > > > > delete all blank cells in WORK ORDER. What I would like to have happen now
> > > > > is have the new condensed list reach a certain point (A63) and the remaining
> > > > > portion of that list move over to F14 and work its way down to cell F57 if
> > > > > needed. If the list continues after that point I would need the remainder to
> > > > > start on J1.
> > > > >
> > > > > I am having fits with this for some reason, so if anyone has a similar code
> > > > > or knows how to go about this, it would be greatly appreciated.
> > > > >
> > > > >

So what you have is a list of data that spans A14 to A63 or more- how many
columns does the data span? How many lines do you want to stop at 43 or 49?



"Cerberus" wrote:

> I am very new at writting VBA codes so any help would be greatly appreciated.
> I have found that hidding the lines has caused issues like you said it would
>
>
> Thanks for all your help on all of this.
>
> "JR Form" wrote:
>
> > Hi Cerberus,
> >
> > You are hiding the rows of information. This could cause a problem when you
> > try pasteing information to adjacen columns. Is there a reason you are not
> > deleting the row?
> > If you want wriete a line to return to the top of the range you are
> > evaluating for the line count then run the code sinpet I gae you.
> > You may want to write a function to break the line counts into copy segments
> > to move them. Do you need help with this?
> >
> >
> > "Cerberus" wrote:
> >
> > > Thank you for replying. I have not had time to try your idea yet, I had to
> > > take time off to move (fun times). Here is the code I have used to remove
> > > blank spaces:
> > >
> > > Private Sub Worksheet_Activate()
> > >
> > > Dim HiddenRow&, RowRange As Range
> > >
> > > '*****************************
> > > '< Set the 1st & last rows to be hidden >
> > > Const FirstRow As Long = 1
> > > Const LastRow As Long = 2000
> > >
> > > '< Set the column that contains data >
> > > Const DataCol As String = "A"
> > >
> > > '*****************************
> > >
> > > ActiveWindow.DisplayZeros = False
> > > Application.ScreenUpdating = False
> > >
> > > For HiddenRow = FirstRow To LastRow
> > >
> > > 'Take the Length of the value in column A of each row
> > > If Len(Range(DataCol & HiddenRow).Value) <> 0 Then
> > > 'there's something in this row - don't hide
> > > Rows(HiddenRow).EntireRow.Hidden = False
> > > Else
> > > 'there's nothing in this row yet - hide it
> > > Rows(HiddenRow).EntireRow.Hidden = True
> > > End If
> > >
> > > Next HiddenRow
> > >
> > > Application.ScreenUpdating = True
> > >
> > > End Sub
> > >
> > >
> > > "JR Form" wrote:
> > >
> > > > After you run your code to condense lines then do a count of the lines
> > > >
> > > > With ActiveCell
> > > > i = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
> > > > End With
> > > >
> > > > calculate how many breaks you need and move the records using a case
> > > > structure.
> > > >
> > > > Hope this helps
> > > >
> > > > Maybe I could see the code you are using already?
> > > >
> > > > "Cerberus" wrote:
> > > >
> > > > > I have created a sheet called WORK ORDER where all of my product options are
> > > > > listed. I made it so that if an option was not picked, the cell that
> > > > > contained that option would be blank. I then wrote a code in VBE that would
> > > > > delete all blank cells in WORK ORDER. What I would like to have happen now
> > > > > is have the new condensed list reach a certain point (A63) and the remaining
> > > > > portion of that list move over to F14 and work its way down to cell F57 if
> > > > > needed. If the list continues after that point I would need the remainder to
> > > > > start on J1.
> > > > >
> > > > > I am having fits with this for some reason, so if anyone has a similar code
> > > > > or knows how to go about this, it would be greatly appreciated.
> > > > >
> > > > >

What I have is a list that if all options are indicated would take up
A1:C189. I made it so everything that is indicated in column C is referenced
to column A of the same row. So if A is blank so are B and C.

A1:A26 is static and will never be blank. So, when the list reaches the
63rd row in column A, I would like the remaining list to move over to F14 and
work its way down 43 more rows. In most cases that will do the trick because
our work orders rarely go on to a second page. In the rare case it does move
on to a second page I would assume it would be easiest to place the remaining
list at J1.

If you would like to see what I have done on this spreadsheet I would be
more then happy to send you a copy of it to you so you could possibly get a
better understanding of what I’m trying to do.

Thanks again for your assistance; I know this is not your job so doing this
at you leisure is very kind.


"JR Form" wrote:

> So what you have is a list of data that spans A14 to A63 or more- how many
> columns does the data span? How many lines do you want to stop at 43 or 49?
>
>
>
> "Cerberus" wrote:
>
> > I am very new at writting VBA codes so any help would be greatly appreciated.
> > I have found that hidding the lines has caused issues like you said it would
> >
> >
> > Thanks for all your help on all of this.
> >
> > "JR Form" wrote:
> >
> > > Hi Cerberus,
> > >
> > > You are hiding the rows of information. This could cause a problem when you
> > > try pasteing information to adjacen columns. Is there a reason you are not
> > > deleting the row?
> > > If you want wriete a line to return to the top of the range you are
> > > evaluating for the line count then run the code sinpet I gae you.
> > > You may want to write a function to break the line counts into copy segments
> > > to move them. Do you need help with this?
> > >
> > >
> > > "Cerberus" wrote:
> > >
> > > > Thank you for replying. I have not had time to try your idea yet, I had to
> > > > take time off to move (fun times). Here is the code I have used to remove
> > > > blank spaces:
> > > >
> > > > Private Sub Worksheet_Activate()
> > > >
> > > > Dim HiddenRow&, RowRange As Range
> > > >
> > > > '*****************************
> > > > '< Set the 1st & last rows to be hidden >
> > > > Const FirstRow As Long = 1
> > > > Const LastRow As Long = 2000
> > > >
> > > > '< Set the column that contains data >
> > > > Const DataCol As String = "A"
> > > >
> > > > '*****************************
> > > >
> > > > ActiveWindow.DisplayZeros = False
> > > > Application.ScreenUpdating = False
> > > >
> > > > For HiddenRow = FirstRow To LastRow
> > > >
> > > > 'Take the Length of the value in column A of each row
> > > > If Len(Range(DataCol & HiddenRow).Value) <> 0 Then
> > > > 'there's something in this row - don't hide
> > > > Rows(HiddenRow).EntireRow.Hidden = False
> > > > Else
> > > > 'there's nothing in this row yet - hide it
> > > > Rows(HiddenRow).EntireRow.Hidden = True
> > > > End If
> > > >
> > > > Next HiddenRow
> > > >
> > > > Application.ScreenUpdating = True
> > > >
> > > > End Sub
> > > >
> > > >
> > > > "JR Form" wrote:
> > > >
> > > > > After you run your code to condense lines then do a count of the lines
> > > > >
> > > > > With ActiveCell
> > > > > i = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
> > > > > End With
> > > > >
> > > > > calculate how many breaks you need and move the records using a case
> > > > > structure.
> > > > >
> > > > > Hope this helps
> > > > >
> > > > > Maybe I could see the code you are using already?
> > > > >
> > > > > "Cerberus" wrote:
> > > > >
> > > > > > I have created a sheet called WORK ORDER where all of my product options are
> > > > > > listed. I made it so that if an option was not picked, the cell that
> > > > > > contained that option would be blank. I then wrote a code in VBE that would
> > > > > > delete all blank cells in WORK ORDER. What I would like to have happen now
> > > > > > is have the new condensed list reach a certain point (A63) and the remaining
> > > > > > portion of that list move over to F14 and work its way down to cell F57 if
> > > > > > needed. If the list continues after that point I would need the remainder to
> > > > > > start on J1.
> > > > > >
> > > > > > I am having fits with this for some reason, so if anyone has a similar code
> > > > > > or knows how to go about this, it would be greatly appreciated.
> > > > > >
> > > > > >

Go ahead and send me the file.

(E-Mail Removed)

"Cerberus" wrote:

> What I have is a list that if all options are indicated would take up
> A1:C189. I made it so everything that is indicated in column C is referenced
> to column A of the same row. So if A is blank so are B and C.
>
> A1:A26 is static and will never be blank. So, when the list reaches the
> 63rd row in column A, I would like the remaining list to move over to F14 and
> work its way down 43 more rows. In most cases that will do the trick because
> our work orders rarely go on to a second page. In the rare case it does move
> on to a second page I would assume it would be easiest to place the remaining
> list at J1.
>
> If you would like to see what I have done on this spreadsheet I would be
> more then happy to send you a copy of it to you so you could possibly get a
> better understanding of what I’m trying to do.
>
> Thanks again for your assistance; I know this is not your job so doing this
> at you leisure is very kind.
>
>
> "JR Form" wrote:
>
> > So what you have is a list of data that spans A14 to A63 or more- how many
> > columns does the data span? How many lines do you want to stop at 43 or 49?
> >
> >
> >
> > "Cerberus" wrote:
> >
> > > I am very new at writting VBA codes so any help would be greatly appreciated.
> > > I have found that hidding the lines has caused issues like you said it would
> > >
> > >
> > > Thanks for all your help on all of this.
> > >
> > > "JR Form" wrote:
> > >
> > > > Hi Cerberus,
> > > >
> > > > You are hiding the rows of information. This could cause a problem when you
> > > > try pasteing information to adjacen columns. Is there a reason you are not
> > > > deleting the row?
> > > > If you want wriete a line to return to the top of the range you are
> > > > evaluating for the line count then run the code sinpet I gae you.
> > > > You may want to write a function to break the line counts into copy segments
> > > > to move them. Do you need help with this?
> > > >
> > > >
> > > > "Cerberus" wrote:
> > > >
> > > > > Thank you for replying. I have not had time to try your idea yet, I had to
> > > > > take time off to move (fun times). Here is the code I have used to remove
> > > > > blank spaces:
> > > > >
> > > > > Private Sub Worksheet_Activate()
> > > > >
> > > > > Dim HiddenRow&, RowRange As Range
> > > > >
> > > > > '*****************************
> > > > > '< Set the 1st & last rows to be hidden >
> > > > > Const FirstRow As Long = 1
> > > > > Const LastRow As Long = 2000
> > > > >
> > > > > '< Set the column that contains data >
> > > > > Const DataCol As String = "A"
> > > > >
> > > > > '*****************************
> > > > >
> > > > > ActiveWindow.DisplayZeros = False
> > > > > Application.ScreenUpdating = False
> > > > >
> > > > > For HiddenRow = FirstRow To LastRow
> > > > >
> > > > > 'Take the Length of the value in column A of each row
> > > > > If Len(Range(DataCol & HiddenRow).Value) <> 0 Then
> > > > > 'there's something in this row - don't hide
> > > > > Rows(HiddenRow).EntireRow.Hidden = False
> > > > > Else
> > > > > 'there's nothing in this row yet - hide it
> > > > > Rows(HiddenRow).EntireRow.Hidden = True
> > > > > End If
> > > > >
> > > > > Next HiddenRow
> > > > >
> > > > > Application.ScreenUpdating = True
> > > > >
> > > > > End Sub
> > > > >
> > > > >
> > > > > "JR Form" wrote:
> > > > >
> > > > > > After you run your code to condense lines then do a count of the lines
> > > > > >
> > > > > > With ActiveCell
> > > > > > i = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
> > > > > > End With
> > > > > >
> > > > > > calculate how many breaks you need and move the records using a case
> > > > > > structure.
> > > > > >
> > > > > > Hope this helps
> > > > > >
> > > > > > Maybe I could see the code you are using already?
> > > > > >
> > > > > > "Cerberus" wrote:
> > > > > >
> > > > > > > I have created a sheet called WORK ORDER where all of my product options are
> > > > > > > listed. I made it so that if an option was not picked, the cell that
> > > > > > > contained that option would be blank. I then wrote a code in VBE that would
> > > > > > > delete all blank cells in WORK ORDER. What I would like to have happen now
> > > > > > > is have the new condensed list reach a certain point (A63) and the remaining
> > > > > > > portion of that list move over to F14 and work its way down to cell F57 if
> > > > > > > needed. If the list continues after that point I would need the remainder to
> > > > > > > start on J1.
> > > > > > >
> > > > > > > I am having fits with this for some reason, so if anyone has a similar code
> > > > > > > or knows how to go about this, it would be greatly appreciated.
> > > > > > >
> > > > > > >