Selecting Range Based on Cell Content then Copy/Paste to New Sheet,Looping

[pwk]

I have a worksheet that consists of 13 columns and multiple varying
rows of data of 4 to 10 rows set up like the example (column M)
starting on row 7. What I would like to accomplish is to select the
areas containing 2 on the last row last column up to blank row and
paste them to a new sheet looping through entire worksheet. I’ve tried
using ActiveCell.CurrentRegion.Select but there are blank columns in
some areas. I’ve also looked at xlUp and think it may work but I’ve
only been able to select cells not rows. Altogether, I’m having
trouble selecting the areas as well as looping through sheet with 264
rows of varying row sizes of data. I’ve looked through previous
postings and haven’t seen a similar problem. I hope my example is
clear enough.
Thanks, Paul

Blank Row
1
1
1
1
1
1
Blank Row
1
2
2
2
Blank row

 

[Joel]

Use something likje the code below..what copying cells the source and
destination types must be the same.

a) Copy range to Range
b) Copy row(s) to row
c) copy column(s) to column
d) Entire Sheet to Entire sheet (select all cells)
e) Sheet to sheet (using worksheet name instead of selecting all cells)

when copying more the one item as the source only use the first item in the
destination, otherwise, if the size are not the same you get weird results.
for example copying 2 rows to 3 rows. It is better to copy the 2 rows to the
first new row and excel will automatically copy both rows.

LastRow = Range("M" & Rows.count).end(xlup).row
RowCount = 1
StartRow = RowCount
Do While RowCount > = LastRow
if Range("M" & (MowCount + 1) = "" then
set CopyRange = Rows(StartRow & ":" & RowCount)
CopyRange.copy destination:=Sheets("Sheet2").Rows(1)
StartRow = RowCount + 2

end if

RowCount = RowCount + 1
loop

 

[Don Guillett]

areas containing 2 on ??
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You send a clear explanation of what you want
3. You send before/after examples and expected results.

This will find and copy the block of rows.
Sub FINDLastValue()
Dim fn As String
mc = 7 ' Column G
On Error GoTo nofind
fn = InputBox("Enter the num")
lv = Columns(mc).Find(What:=fn, LookAt:=xlWhole, _
SearchDirection:=xlPrevious).Row
MsgBox lv
ur = Cells(lv, mc).End(xlUp).Row - 1
MsgBox ur
Range(Cells(ur, mc), Cells(lv, mc)).EntireRow.Copy
Exit Sub
nofind:
MsgBox "Not found"
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
I have a worksheet that consists of 13 columns and multiple varying
rows of data of 4 to 10 rows set up like the example (column M)
starting on row 7. What I would like to accomplish is to select the
areas containing 2 on the last row last column up to blank row and
paste them to a new sheet looping through entire worksheet. I’ve tried
using ActiveCell.CurrentRegion.Select but there are blank columns in
some areas. I’ve also looked at xlUp and think it may work but I’ve
only been able to select cells not rows. Altogether, I’m having
trouble selecting the areas as well as looping through sheet with 264
rows of varying row sizes of data. I’ve looked through previous
postings and haven’t seen a similar problem. I hope my example is
clear enough.
Thanks, Paul

Blank Row
1
1
1
1
1
1
Blank Row
1
2
2
2
Blank row