select only certain cells in a columns
 

How would I write an if statement to select all the cells in a column that are marked true, and then move those true rows somewhere else?

select only certain cells in a columns
 

Dim rng as Range, cell as Range, rng1 as Range
set rng = Range(cells(1,"C"),Cells(rows.count,"C").End(xlup) )
set rng1 = nothing
for each cell in rng
if cell.value = "True" then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
Next
Next
if not rng1 is nothing then
rng1.entirerow.copy Destination:=worksheets("Sheet2").Range("A1")
rng1.EntireRow.delete
End if

code is untested.
--
regards,
Tom Ogilvy

"desperate" wrote in message
...
How would I write an if statement to select all the cells in a column that
are marked true, and then move those true rows somewhere else?

select only certain cells in a columns
 

Here's a solution that takes the rows from one sheet and moves to another,
with no looping

Dim oldSh As Worksheet
With ActiveSheet
Set oldSh = ActiveSheet
.Range("D1").EntireRow.Insert
.Columns("D:D").AutoFilter Field:=1, Criteria1:="TRUE"
.Cells.SpecialCells(xlCellTypeVisible).Copy
End With
Worksheets("Sheet2").Activate
Range("A1").Select
ActiveSheet.Paste
oldSh.Activate
With ActiveSheet
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.D elete
.Rows("1:1").Delete Shift:=xlUp
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"desperate" wrote in message
...
How would I write an if statement to select all the cells in a column that
are marked true, and then move those true rows somewhere else?

select only certain cells in a columns
 

Thank you. This coding helps very much, but I don't want to cut the rows from the first sheet, I want to just copy them onto the second sheet. How would I do that?

select only certain cells in a columns
 

Hi
just delete the last line:
rng1.EntireRow.delete

--
Regards
Frank Kabel
Frankfurt, Germany

desperate wrote:
Thank you. This coding helps very much, but I don't want to cut the
rows from the first sheet, I want to just copy them onto the second
sheet. How would I do that?

select only certain cells in a columns
 

You said "move", so if you don't want to retain the values in the original
and you don't want to delete, then you must want to clear:

Dim rng as Range, cell as Range, rng1 as Range
set rng = Range(cells(1,"C"),Cells(rows.count,"C").End(xlup) )
set rng1 = nothing
for each cell in rng
if cell.value = "True" then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
Next
Next
if not rng1 is nothing then
rng1.entirerow.copy Destination:=worksheets("Sheet2").Range("A1")
rng1.EntireRow.clearContents
End if

--
Regards,
Tom Ogilvy


"desperate" wrote in message
...
Thank you. This coding helps very much, but I don't want to cut the rows
from the first sheet, I want to just copy them onto the second sheet. How
would I do that?