select only certain cells in a columns

[Guest]

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?

 

[Tom Ogilvy]

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

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?

 

[Bob Phillips]

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").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.Delete
.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)

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?

 

[Guest]

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?

 

[Frank Kabel]

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

 

[Tom Ogilvy]

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

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?