Search for any value within a range and copy Solved/Closed

Question

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

venkat1926 · Answer

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

venkat1926 · Answer

"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))

Sarah89 · Answer

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") ?

venkat1926 · Answer

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

Sarah89 · Answer

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

venkat1926 · Answer

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

sarah89 · Answer

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!

venkat1926 · Answer

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

Search for any value within a range and copy

8 responses

Similar discussions

Subscribe To Our Newsletter!