Search for any value within a range and copy

Solved/Closed
Sarah89 - Dec 7, 2011 at 05:39 AM
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
- Dec 29, 2011 at 06:45 AM
Hello,


I am a beginner with Macros and would like to find code that will allow me to do the following:

1. Look for ANY values present in columns K-O of worksheet named "Data Dump".
2. For any value present, copy Entire row to new sheet within the same workbook cell A2.

I have been searching online for a while but everything found has been very specific to circumstances and I do not have the knowledge to be able to alter to my needs.

Thanks, Sarah

8 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Dec 7, 2011 at 09:19 PM
try this macro

Sub test()
Dim r As Range, c As Range
Worksheets("A2").Cells.Clear
Worksheets("sheet1").Activate
Set r = Range(Range("K1"), Range("O1").End(xlDown))
For Each c In r
c.Select
If c = "Data Dump" Then
c.EntireRow.Copy Worksheets("A2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next c
End Sub
0
Hi,

Thanks for the response but unfortunately it returns 'RunTime error 9. Subscript out of range' :S

Any suggestions? Columns M-O (Correction from previous note) Provide an optional response column in which most cells are blank. The cells which are populated need the whole row to be copied to a new sheet within the workbook so all changes that need to be made are compiled.

Thanks Again
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Dec 8, 2011 at 07:41 PM
"Subscript out of range" means there is not sheet as A2

I misunderstood your "new sheet within the same workbook cell A2."

change that statements wherever occurs "worksheets("A2")" (2 places)
change it to correct worksheet name ---replace only A2 by the name of the sheet where you want to have the result.

change this statement

Set r = Range(Range("K1"), Range("O1").End(xlDown))


to

Set r = Range(Range("M1"), Range("O1").End(xlDown))
0
Hi venkat,

This is the macro that I have now ...

Sub test()
Dim r As Range, c As Range
Worksheets("Changes").Cells.Clear
Worksheets("Data Dump").Activate
Set r = Range(Range("M1"), Range("O1").End(xlDown))
For Each c In r
c.Select
If c = "Data Dump" Then
c.EntireRow.Copy Worksheets("Changes").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next c
End Sub


Worksheets("Changes").Cells.Clear >>> This is the worksheet i want the results to be in.

Worksheets("Data Dump").Activate >>> And this is the worksheet that contains the info i want to search for values.

Could you please explain to me this part of the macro?

For Each c In r
c.Select
If c = "Data Dump" Then
c.EntireRow.Copy Worksheets("Changes").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next c
End Sub

If i understand it right, I want 'c' to represent any value present in the range that has been set as 'r'. Then, if a 'c' (a populated cell) has been found, copy entire row and paste to Worksheet("Changes") ?
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Dec 9, 2011 at 05:55 AM
r is the range of cells from M1 to the last cell in column O
suppose you have five rows. then
r is range from M1 to O5
that means 5x3=15 cells
for each c in r
each of these 15 cells is considered one by one
first the first cell N1
so c is M1
c.select
this statement is not necessary . parked there only for debugging.
then it check c (that is M1 is having entry "Data Dump"
if so then it copied the whole row of M1 if not it skips and goes to next c which is N1 .
then the test and decision. etc

when all the 15 cells checked it goes out of "for -----next"
and then if there is nothing else to do closed the macro
0

Didn't find the answer you are looking for?

Ask a question
Thankyou. I am learning! ... Slowly =P

What code would I use to search for any non-blank cell? As I am not looking for specific values or words =S
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Dec 9, 2011 at 09:01 PM
there must be many ways of selecting non blank cells depending upon why you want to do it

suppose there are only constants (no formulas) then

range("A1").CurrentRegion.SpecialCells(xlCellTypeConstants).Select
you solve the problem when the need arise
0
Sub Changes()
Dim r As Range, c As Range

Worksheets("Changes").Cells.Clear
Worksheets("Data Dump").Activate
Set r = Range(Range("N2"), Range("Q2").End(xlDown))
For Each c In r
c.Select
If c.Value <> "" Then
c.EntireRow.Copy Worksheets("Changes").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next c
End Sub

I searched for non-blank formula and the above has now worked =D Thanks for all your help!
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Dec 29, 2011 at 06:45 AM
one small sophistication(nor correction)

quote
If c.Value <> "" Then 

unquote

c is a range. the default property of a range is its value as long as it is one cell
so it is enought
If c<> "" Then 


try
0