Search Range

[DanG]

I am just starting to learn VBA.

What I'd like to do..
1. Find last used cell in column "A". - I know how to do this
2. Cycle through the range and finding specific values (in my case I am looking for district names (North State, South Bay...).
3. Once found I would like to copy that district and the entire row for that district. and move it to another sheet.
4. Final result is the desired district rows on another sheet with no blanks between them.

Here is what I have for the column range...

Sub MyRangeCol()
Dim myrange As Range
Set myrange = Range("$a$1", Range("a65536").End(xlUp))
myrange.Name = "ColumnA" [COLOR="Green"]'(I just did this for fun)[/COLOR]
End Sub

Thank you

 

[Brianwarnock]

I am just starting to learn VBA.

Thank you

Hey you said that back in 2005

 

[DanG]

I'm not sure it's the same thing?

I want to "loop" through the range looking for multiple values and paste those cells entire rows into another sheet.

Here is what I have found that seems to work:

Sub FilterRange()
Dim myrange As Range
Dim CurCell As Object

Dim Cell As Range

Dim rngOutput As Range
Set myrange = Range("$a$1", Range("a65536").End(xlUp))
Set rngOutput = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    
     ''    On  Error Resume Next
      
    For Each Cell In myrange
        If Cell.Value = "dan" Then
            Cell.EntireRow.Copy rngOutput
            Set rngOutput = rngOutput.Offset(1, 0)
        End If
    Next Cell
    Application.CutCopyMode = False

End Sub

And by the way, you are quite the detective

 

[Brianwarnock]

And by the way, you are quite the detective

I knew that I had contributed to a thread on row copying in the past, and searched for it to see what it was about, and lo and behold it was for you
So I knew that you were not a beginner and your code proves it.

Brian

 

[DanG]

Oh no, I am a beginner to be sure.

I have "poked around" in the past, but not "written" anything. I am just now starting to get more seriouse about it. I am now at the point where I can "look" at code and know what's going on and then adapt it to fit what I want to do. But, I do not "write" my own, which is what I am attempting to do now.

The code here I "adapted", now I want to pick it apart and get what's really going on. So with that in mind maybe you would be kind enough to explain a couple of things...

1. why is "curcell" declared as an object?
2. this does what I want for one value ("dan"), how do I include other values (I tried Or("North state",...,...) and I tried the "Or" operator (don't know where that is on the keyboard (I copy/pasted from a web site))?

 

[Brianwarnock]

Well Dan I'm no expert myself, I was never stretched at the charity I worked at and that is now over a year ago. Most of my coding is adapted code. The Find technique I would use is an adaption of a solution posted here only a couple of days ago. ( Thanks due to Chergh)

I don't know why the code Dims an object it doesn't use, but I used it.
I would not use Cell myself as it is too similar to Cells.
It is difficult to descibe how I would do it, but I would put the values that need to be met in a separate sheet and search that using Find.
I attach my solution as a document so that you can choose to try youself or take a peek.

Brian

 

[DanG]

Well Brian, very impressive indeed.
This is exactly what I was looking for, and even more.

I just have to pick it apart to see what makes it tick now.

Thank you very much for the schooling

 

[namliam]

Sounds to me like someone is trying to have a spreadsheet do what a Database was created for... This sounds an awfull lot like a basic query in a database.

Have you considered that your needs actually exceed the bounds of a spreadsheet and moving it to a (Access) Database environment?

 

[DanG]

I could see where one might think that

As I stated, this is in an effort to learn Excel VBA.
But there are times when you want the best of both worlds (Excel/Access) and it is nice to know your tools.