Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a Range When Rows Are Long Variables
I have the following code that does the following:
*Sorts data according to one column B *While the SOB values are all equal in Column B, it goes through data and identifies the bottom and top position of each row and stores it in a Long TopRowPos and Long BottomRowPos variable. I would like to copy that range of data in TopRowPos and Long BottomRowPos but can't because it is not a range value. There is a type mismatch between the long & range variables and I can't assign them. Does anyone have any suggestions? Dim BottomRowPos As Long Dim TopRowPos As Long Dim i As Long Dim SOB As String Dim Same As Boolean Dim WS As Worksheet Dim TopRange As Range Dim BottomRange As Range 'Sort data by Set of Books Set WS = Worksheets("Sheet1") Cells.Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'Identify Range for Each Set of Books BottomRowPos = WS.Range("B65536").End(xlUp).Row While BottomRowPos 1 TopRowPos = BottomRowPos 'While the SOB values are equal While WS.Cells(TopRowPos - 1, 2) = WS.Cells(BottomRowPos, 2) TopRowPos = TopRowPos - 1 Wend Same = True 'Set SOB to BottomRowPos SOB = WS.Cells(BottomRowPos, 2) For i = TopRowPos To BottomRowPos If WS.Cells(i, 2) < SOB Then Same = False If Not Same Then i = BottomRowPos Next i 'Executes if Same is True. Copy range of SOB to new worksheet **This is where I need help** **I have a top & bottom row position but i don't know how to tell it to copy & paste to a new worksheet** Thx! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a Range When Rows Are Long Variables
set rng = ws.Range(ws.cells(TopRowPos,2),ws.Cells(BottmRowPo s,2))
rng.copy or rng.Resize(,10).copy to copy 10 columns wide as an example - adjust to fit your needs. -- Regards, Tom Ogilvy "ALATL" wrote: I have the following code that does the following: *Sorts data according to one column B *While the SOB values are all equal in Column B, it goes through data and identifies the bottom and top position of each row and stores it in a Long TopRowPos and Long BottomRowPos variable. I would like to copy that range of data in TopRowPos and Long BottomRowPos but can't because it is not a range value. There is a type mismatch between the long & range variables and I can't assign them. Does anyone have any suggestions? Dim BottomRowPos As Long Dim TopRowPos As Long Dim i As Long Dim SOB As String Dim Same As Boolean Dim WS As Worksheet Dim TopRange As Range Dim BottomRange As Range 'Sort data by Set of Books Set WS = Worksheets("Sheet1") Cells.Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'Identify Range for Each Set of Books BottomRowPos = WS.Range("B65536").End(xlUp).Row While BottomRowPos 1 TopRowPos = BottomRowPos 'While the SOB values are equal While WS.Cells(TopRowPos - 1, 2) = WS.Cells(BottomRowPos, 2) TopRowPos = TopRowPos - 1 Wend Same = True 'Set SOB to BottomRowPos SOB = WS.Cells(BottomRowPos, 2) For i = TopRowPos To BottomRowPos If WS.Cells(i, 2) < SOB Then Same = False If Not Same Then i = BottomRowPos Next i 'Executes if Same is True. Copy range of SOB to new worksheet **This is where I need help** **I have a top & bottom row position but i don't know how to tell it to copy & paste to a new worksheet** Thx! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a Range When Rows Are Long Variables
hmm..... this isn't quite giving me the result i am looking for.
The copy seems to work as I see all of the cells selected but it never pastes anything. I will need it to paste to the next empty cell in the destination worksheet. I appreciate your time! "Tom Ogilvy" wrote: set rng = ws.Range(ws.cells(TopRowPos,2),ws.Cells(BottmRowPo s,2)) rng.copy or rng.Resize(,10).copy to copy 10 columns wide as an example - adjust to fit your needs. -- Regards, Tom Ogilvy "ALATL" wrote: I have the following code that does the following: *Sorts data according to one column B *While the SOB values are all equal in Column B, it goes through data and identifies the bottom and top position of each row and stores it in a Long TopRowPos and Long BottomRowPos variable. I would like to copy that range of data in TopRowPos and Long BottomRowPos but can't because it is not a range value. There is a type mismatch between the long & range variables and I can't assign them. Does anyone have any suggestions? Dim BottomRowPos As Long Dim TopRowPos As Long Dim i As Long Dim SOB As String Dim Same As Boolean Dim WS As Worksheet Dim TopRange As Range Dim BottomRange As Range 'Sort data by Set of Books Set WS = Worksheets("Sheet1") Cells.Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'Identify Range for Each Set of Books BottomRowPos = WS.Range("B65536").End(xlUp).Row While BottomRowPos 1 TopRowPos = BottomRowPos 'While the SOB values are equal While WS.Cells(TopRowPos - 1, 2) = WS.Cells(BottomRowPos, 2) TopRowPos = TopRowPos - 1 Wend Same = True 'Set SOB to BottomRowPos SOB = WS.Cells(BottomRowPos, 2) For i = TopRowPos To BottomRowPos If WS.Cells(i, 2) < SOB Then Same = False If Not Same Then i = BottomRowPos Next i 'Executes if Same is True. Copy range of SOB to new worksheet **This is where I need help** **I have a top & bottom row position but i don't know how to tell it to copy & paste to a new worksheet** Thx! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question about copying long rows of data | Excel Discussion (Misc queries) | |||
Selecting Rows using Long Variables | Excel Discussion (Misc queries) | |||
Copying a formula that contains a range of cell rows | Excel Discussion (Misc queries) | |||
How long do variables retain their values | Excel Programming | |||
Grouping Rows with Range Variables | Excel Programming |