Posted to microsoft.public.excel.programming
|
|
Simple copy rows with certain value to new sheet
Thats the first time I have seen an explanation of Xlup that was
understandable. Many thanks
Diverdon
"Tom Ogilvy" wrote in message
...
If you want to the very bottom of the sheet in column K and then hit the
End
key and the up arrow, the selected cell would then be the last cell in
that
column that contains data, so Cells(rows.count,"K").End(xlup) is
equivalent
to that.
If I then do Range(start reference, end reference) I can refer to the
part
of a column. In this case, I reference the first cell (cells(1,"K") and
the
last cell with data Cells(rows.count,"K").End(xlup)
now I loop through this reference
for each cell in rng
rw refers the row I want to work with on the second sheet.
So if I find a value containing 6, I want to write that to the second
sheet.
I would write it in row rw.
Worksheets("Sheet2").Cells(rw,1)
now increment rw to refer to the next location where i want to write the
next found value.
I start on row 2 because this appears to be a database type worksheet and
I
assume the first row will have the column headers.
not that these unqualified references refer to the active sheet
set rng = Range(Cells(1,"K"),Cells(rows.count,"K").End(xlup) )
If I wanted to refer to sheet3 for instance and it is not active I would
need to qualify the references
With Worksheets("Sheet3")
set rng = .Range(.Cells(1,"K"),.Cells(rows.count,"K").End(xl up))
End With
precede the Range and Cells with the period/fullstop
--
Regards,
Tom Ogilvy
Shetty wrote in message
om...
Hi Tom,
Sorry to jump in between. Just to understand some logic.
1. set rng = Range(Cells(1,"K"),Cells(rows.count,"K").End(xlup) )
Pl explain what it does and how it works.
2. When you have already used a loop (for each cell in rng) why do you
need
to use rw = rw + 1 ? Moreover you set rw=2. Why not rw=1?
Pl bear with me and explain it so that I can also use such compect
statements instead of 5-6 statements and avoid select method.
Regards,
Shetty
"Tom Ogilvy" wrote in message
...
Dim rng as Range, cell as Cell
Dim rw as Long
rw = 2
set rng = Range(Cells(1,"K"),Cells(rows.count,"K").End(xlup) )
for each cell in rng
if cell.Value = 6 then
cell.Entirerow.copy Destination:=worksheets("Sheet2") _
.Cells(rw,1)
rw = rw + 1
end if
Next
another approach would be to click in you data, do
Data=Filter=Autofilter
go to column k and in the dropdown select your values.
Use the row borders to select all the data, then do Edit=Copy
go to A1 on the new sheet and do Edit=Paste
This will copy just the visible rows (which should be the rows that
contain
your value).
Go back to the original sheet and do Data=Filter=Autofilter to
remove
the
autofilter.
If you want a macro, turn on the macro recorder while you do this
manually.
--
Regards,
Tom Ogilvy
"burlybo" wrote in message
...
I have been looking everywhere for code to simply copy the rows from
one
sheet that have a certain value in column K to a new sheet. I can't
find the code and I'm fairly new to this stuff.
I found out how to turn the cells with the value I'm looking for
red,
but I couldn't get the rows to copy into a new sheet.
Is this easy? It seems to me it should be easy, right? Should I use
a
select statement?
---
Message posted from http://www.ExcelForum.com/
|