copy rows in range if data in first cell

[Wes_A]

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?

 

[Chip Pearson]

Try some code like the following:

Sub AAA()
Dim Destination As Range
Dim RowNumber As Long
Set Destination = Worksheets(2).Range("A1")
For RowNumber = 1 To 10 '<<<< CHANGE RowNumber as needed
If Len(Cells(RowNumber, "A").Text) > 0 Then
Rows(RowNumber).Copy Destination:=Destination
Set Destination = Destination(2, 1)
End If
Next RowNumber
End Sub

Change the row number from 1 to 10 to whatever rows you want to
process.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[michdenis]

Hi,

For cells containing Text only

'---------------------------
Sub test()
Dim Rg As Range

On Error Resume Next
With Sheet1
With .Range("A1:A" & .Range("A65536").End(xlUp).Row)
Set Rg = .SpecialCells(xlCellTypeFormulas, xlTextValues)
End With
End With
Rg.EntireRow.Copy Sheet2.Range("A1")

End Sub
'---------------------------



"Wes_A" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
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?

 

[michdenis]

If you want Text and numerical stemming from formula

'-------------------------------
Sub test()
Dim Rg As Range

On Error Resume Next
With Sheet1
With .Range("A1:A" & .Range("A65536").End(xlUp).Row)
Set Rg = .SpecialCells(xlCellTypeFormulas, 3)
End With
End With
Rg.EntireRow.Copy Sheet2.Range("A1")

End Sub
'-------------------------------


"michdenis" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
Hi,

For cells containing Text only

'---------------------------
Sub test()
Dim Rg As Range

On Error Resume Next
With Sheet1
With .Range("A1:A" & .Range("A65536").End(xlUp).Row)
Set Rg = .SpecialCells(xlCellTypeFormulas, xlTextValues)
End With
End With
Rg.EntireRow.Copy Sheet2.Range("A1")

End Sub
'---------------------------



"Wes_A" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
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?

 

[Rick Rothstein]

It does not sound like you are looking for a code solution even though you
posted in a programming newsgroup. If that is the case, then assuming Column
A is your range's first cell's column, select Column A and click
Data/Filter/AutoFilter. You can now deselect the column and then click the
dropdown arrow that appeared in Cell A1... click "(NonBlanks)" from the list
that appears. You can now select the visible rows and copy/paste them
wherever you need them to be copied to. When you are done, just click
Data/Filter/AutoFilter again (you do not have to select the column this
time) to remove the filter and return your worksheet back to "normal".

 

[Jef Gorbach]

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

 

[Wes_A]

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]

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.