Prev: Deleting rows when cell has "#DIV/0!" error
Next: Sheet name changing dilemma
From: Wes_A on 19 Apr 2010 12:25
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: Chip Pearson on 19 Apr 2010 12:57
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




On Mon, 19 Apr 2010 09:25:01 -0700, Wes_A
<WesA(a)discussions.microsoft.com> 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: michdenis on 19 Apr 2010 13:26
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" <WesA(a)discussions.microsoft.com> a écrit dans le message de groupe de discussion :
3E741A13-96B0-4CBA-9912-E9C0F0412419(a)microsoft.com...
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: michdenis on 19 Apr 2010 13:38
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" <michdenis(a)hotmail.com> a écrit dans le message de groupe de discussion :
095A9554-26C3-4F80-998A-91030D14F22D(a)microsoft.com...
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" <WesA(a)discussions.microsoft.com> a écrit dans le message de groupe de discussion :
3E741A13-96B0-4CBA-9912-E9C0F0412419(a)microsoft.com...
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: Rick Rothstein on 19 Apr 2010 13:40
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".

--
Rick (MVP - Excel)



"Wes_A" <WesA(a)discussions.microsoft.com> wrote in message
news:3E741A13-96B0-4CBA-9912-E9C0F0412419(a)microsoft.com...
> 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?

 |  Next  |  Last
Pages: 1 2
Prev: Deleting rows when cell has "#DIV/0!" error
Next: Sheet name changing dilemma