Prev: Deleting rows when cell has "#DIV/0!" error
Next: Sheet name changing dilemma
From: Jef Gorbach on 19 Apr 2010 14:51
This should give you a starting point.
It filters the range for those rows where column(a) isn't blank,
copies them to Sheet2, then removes those rows where column(a) = zero
(ie didn't have data). Obviously it would be more efficient if anyone
knows a way of filtering where column(a) has results so the second
filter would be unnecessary.

Sub FilterCopy()
Dim CopyRange As Range
'------ copy the rows with formulas within the range ---
Set CopyRange = Range("A2:C100")
CopyRange.AutoFilter field:=1, Criteria1:="<>"
CopyRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Sheet2").Range("A2")
Cells.AutoFilter 'turn filter off
'------- now remove the zero fomula rows ------------------
Worksheets("Sheet2").Activate
Cells.AutoFilter field:=1, Criteria1:="0"
Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete
Cells.AutoFilter
Application.CutCopyMode = False
End Sub
From: Wes_A on 19 Apr 2010 22:47
Wow! Thank you folks for the different suggestions, I will try and see which
works best, but will keep all on record for future ref.
I find this forum so very helpful since I am no expert in programming, but
self taught. Thank you all so much for the recommendations.
Wes

"Wes_A" wrote:

> I need to select from a range of rows - only those having data (formula
> result) in the first cell. There will be some rows without data in the first
> cell but they would contain a formula - these shoudl not be selected for copy.
> I want to select and copy all the rows having data in the first cell in
> order to paste these rows as values into a separate sheet in another workbook.
> Any suggestion?
From: Wes_A on 20 Apr 2010 02:15
I triued the suggestion by Chip Pearson using the following code which seems
to be working:
Dim Destination As Range
Dim RowNumber As Long
Set Destination = Worksheets(2).Range("A1")
For RowNumber = 8 To 508
If Len(Cells(RowNumber, "A").Text) > 0 Then
Rows(RowNumber).Copy Destination:=Destination
Set Destination = Destination(2, 1)
End If
Next RowNumber

However, I need more help on this.
Firstly, I am wanting to copy into a specific sheet in a different Workbook,
not another sheet in the same one. (The destination Workbook will be one of
several others open at the same time.)
Secondly I want to paste "Values Onlly"
Thirdly, I want the data to the next row having no data in the first column
in the destination Workbook. i.e. to be added in rows below data previously
posted there by this macro.

I tried making changes to the code to achieve this but I guess it's beyond
my knowledge since it does not work.

Please assist.

"Wes_A" wrote:

> Wow! Thank you folks for the different suggestions, I will try and see which
> works best, but will keep all on record for future ref.
> I find this forum so very helpful since I am no expert in programming, but
> self taught. Thank you all so much for the recommendations.
> Wes
>
> "Wes_A" wrote:
>
> > I need to select from a range of rows - only those having data (formula
> > result) in the first cell. There will be some rows without data in the first
> > cell but they would contain a formula - these shoudl not be selected for copy.
> > I want to select and copy all the rows having data in the first cell in
> > order to paste these rows as values into a separate sheet in another workbook.
> > Any suggestion?
First  |  Prev  | 
Pages: 1 2
Prev: Deleting rows when cell has "#DIV/0!" error
Next: Sheet name changing dilemma