Hi all

I want to find rows with value in it and copy them to a new sheet named
"Processed" in the same workbook and delete the rows leaving the rows that
are with blank or #NA intact in the sheet.

all help is appreciate.

TIA

regards, xlsops

If you delete everything that is not blank then you get a blank sheet. why
not just clear the enire sheet????? The code below does the copying not the
delete.


Sub PackRows()

Set SourceSht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")

NewRowCount = 1

With SourceSht
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
For OldRowCount = 1 To LastRow
LastCol = .Cells(OldRowCount, Columns.Count). _
End(xlToLeft).Column
If (LastCol 1) Or .Cells(OldRowCount, "A") < "" Then
.Rows(OldRowCount).Copy _
Destination:=DestSht.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next OldRowCount
End With
End Sub



Sub PackRows()

Set SourceSht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")

NewRowCount = 1

With SourceSht
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
For OldRowCount = 1 To LastRow
LastCol = .Cells(OldRowCount, Columns.Count). _
End(xlToLeft).Column
If (LastCol 1) Or .Cells(OldRowCount, "A") < "" Then
.Rows(OldRowCount).Copy _
Destination:=DestSht.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next OldRowCount
End With
End Sub


"franciz" wrote:

Hi all

I want to find rows with value in it and copy them to a new sheet named
"Processed" in the same workbook and delete the rows leaving the rows that
are with blank or #NA intact in the sheet.

all help is appreciate.

TIA

regards, xlsops

Hi Joel

I may not have made myself clear on this, I apologise.

Let say I have from col A to col D with data from row A2 to D10
I want to copy all row that have "m" in col C to sheet named "Processed" and
after copy these rows over, I want to delete these rows which have a rate
such as eg "30.50" in col C leaving rows that does are blank or #NA in col C
so that there will be a value filling up in these blank cells / #NA in col C
on the next batch of Vlookup.

Thanks for your assistance.

regards, francis





"Joel" wrote:

If you delete everything that is not blank then you get a blank sheet. why
not just clear the enire sheet????? The code below does the copying not the
delete.


Sub PackRows()

Set SourceSht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")

NewRowCount = 1

With SourceSht
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
For OldRowCount = 1 To LastRow
LastCol = .Cells(OldRowCount, Columns.Count). _
End(xlToLeft).Column
If (LastCol 1) Or .Cells(OldRowCount, "A") < "" Then
.Rows(OldRowCount).Copy _
Destination:=DestSht.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next OldRowCount
End With
End Sub



Sub PackRows()

Set SourceSht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")

NewRowCount = 1

With SourceSht
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
For OldRowCount = 1 To LastRow
LastCol = .Cells(OldRowCount, Columns.Count). _
End(xlToLeft).Column
If (LastCol 1) Or .Cells(OldRowCount, "A") < "" Then
.Rows(OldRowCount).Copy _
Destination:=DestSht.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next OldRowCount
End With
End Sub


"franciz" wrote:

Hi all

I want to find rows with value in it and copy them to a new sheet named
"Processed" in the same workbook and delete the rows leaving the rows that
are with blank or #NA intact in the sheet.

all help is appreciate.

TIA

regards, xlsops

I modified the code below to add a filter value and to delete the row after
if is copies. You had double quotes around "30.50" so I wasn't sure if you
have this number as text or a reqular number. Modify FiltData as required.
You also said that you had blnak rows so I'm not sure iy you need the row
after the deleted row removed so I did nothing. If you have formulas that
you need to keep I can change the code to just to clear the row instead of
deleting the row.


Sub PackRows()

FiltData = 30.50

Set SourceSht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")

NewRowCount = 1

With SourceSht
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
For OldRowCount = 1 To LastRow

if .Range("C" & OldRowCount).Value = FiltData
.Rows(OldRowCount).Copy _
Destination:=DestSht.Rows(NewRowCount)

.Rows(OldRowCount).Delete

NewRowCount = NewRowCount + 1
End If
Next OldRowCount
End With
End Sub


"franciz" wrote:

Hi Joel

I may not have made myself clear on this, I apologise.

Let say I have from col A to col D with data from row A2 to D10
I want to copy all row that have "m" in col C to sheet named "Processed" and
after copy these rows over, I want to delete these rows which have a rate
such as eg "30.50" in col C leaving rows that does are blank or #NA in col C
so that there will be a value filling up in these blank cells / #NA in col C
on the next batch of Vlookup.

Thanks for your assistance.

regards, francis





"Joel" wrote:

If you delete everything that is not blank then you get a blank sheet. why
not just clear the enire sheet????? The code below does the copying not the
delete.


Sub PackRows()

Set SourceSht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")

NewRowCount = 1

With SourceSht
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
For OldRowCount = 1 To LastRow
LastCol = .Cells(OldRowCount, Columns.Count). _
End(xlToLeft).Column
If (LastCol 1) Or .Cells(OldRowCount, "A") < "" Then
.Rows(OldRowCount).Copy _
Destination:=DestSht.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next OldRowCount
End With
End Sub



Sub PackRows()

Set SourceSht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")

NewRowCount = 1

With SourceSht
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
For OldRowCount = 1 To LastRow
LastCol = .Cells(OldRowCount, Columns.Count). _
End(xlToLeft).Column
If (LastCol 1) Or .Cells(OldRowCount, "A") < "" Then
.Rows(OldRowCount).Copy _
Destination:=DestSht.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next OldRowCount
End With
End Sub


"franciz" wrote:

Hi all

I want to find rows with value in it and copy them to a new sheet named
"Processed" in the same workbook and delete the rows leaving the rows that
are with blank or #NA intact in the sheet.

all help is appreciate.

TIA

regards, xlsops