Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy rows in range if data in first cell
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy rows in range if data in first cell
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 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy rows in range if data in first cell
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" a écrit dans le message de groupe de discussion : ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy rows in range if data in first cell
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" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy rows in range if data in first cell
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.De lete Cells.AutoFilter Application.CutCopyMode = False End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy rows in range if data in first cell
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy rows in range if data in first cell
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy a range of cells 15 rows down and repeat to empty cell | Excel Programming | |||
Copy rows of data (eliminating blank rows) from fixed layout | Excel Discussion (Misc queries) | |||
Read through Range and copy Cell data to Array | Excel Programming | |||
Read through Range and copy Cell data to Array | Excel Programming | |||
How to copy data in one cell into different rows | Excel Worksheet Functions |