Simple Excel VBA help? (Looping through columns and/or rows until blank)

[scootermaster]

Heya.

So, I'm trying to do a little VBA project for a friend as a favor, and I'm slooooowly picking things up, and I could use a little help. There are more important things, but I want to try and figure those out on my own.

Anyway, what I have is a list of keywords arranged in columns. So column A will have a list of words, say, 20, and then column B will have a list, say 40, etc etc.

Can someone help me iterate through the lists one column at a time? (I.e. A1, A2, A3...A20, B1, B2, B3...B40, C1) I've tried a number of things, and I can do it if I explicitly know how big all the rows/columns are, but I'd like it to be more general than that.


Thanks in advance.

 

[KLin]

http://support.microsoft.com/kb/299036


"To Search a Dynamic List or a List with an Unknown Number of Rows" is the section you want.

 

[Tweak155]

Learn the end function. End will skip to the next blank or the next filled cell (opposite of current state). So, if you have an empty cell highlighted, it will go to the next filled cell.

So if A1 through An has data, you can do Range("A1").End(xlDown).Row

If you aren't sure if there are gaps, you can go reasonably far enough down the sheet (well past all data) and go up, I.E Range("A60000").End(xlUp).Row

Give them a shot I think you'll figure it out

EDIT:

You can also do columns. A, B, C...n... Range("A1").End(xlRight).Column

 

[scootermaster]

GREAT tips guys.

Thank you SO much.

Also: I hate VBA. There's just something about the paradigm that doesn't appeal to me. I haven't quite got the hang of what's a range, what's a cell, what can be both, etc.

Anyway, here's what I came up with. It's SLOOOOWWWWW as hell. Any tips for, uh, optimizations?

Sub testLoopsFull()
    Dim rowInc As Integer
    Dim colInc As Integer
    Dim finalRow As Integer
    Dim finalCol As Integer
    Dim count As Integer
    Dim sentence As Range
    Dim sentenceList As Range
    
    'Make sure we're on the sentences sheet
    Worksheets("Sheet1").Activate
    Set sentenceList = Sheets("Sheet1").Range("E1").End(xlDown)
   
    For Each sentence In Worksheets("Sheet1").Range("E:E").SpecialCells(xlCellTypeConstants)
        'MsgBox (sentence)
        'Make sure we're on the keywords sheet
        Worksheets("Keywords").Activate
        ' Make sure we're at 3,1,
        ActiveSheet.Cells(3, 1).Select
        ' Get the last column
        finalCol = Range("A3").End(xlToRight).Column
        ' Iterate through columns
        For colInc = 1 To finalCol
            'Select the top of the row
            ActiveSheet.Cells(4, colInc).Select
            ' Set Do loop to stop when an empty cell is reached.
            Do Until IsEmpty(ActiveCell)
                ' Insert your code here.
                'MsgBox (ActiveCell.Value)
                If InStr(sentence, ActiveCell.Value) > 0 Then
                    sentence.EntireRow.Copy
                    Sheets("Sheet3").Select
                    Range("a65536").End(xlUp).Select
                    Selection.Offset(1, 0).Select
                    ActiveSheet.Paste
                End If
                ' Step down 1 row from present location.
                ActiveCell.Offset(1, 0).Select
            Loop
        Next colInc
    Next sentence

End Sub

So, what it does is take a list of sentences, and iterate through a list of keywords, seeing if one of the keywords appears in the sentence. If it does, it copies it to a new sheet. The keywords can be phrases, case insensitive, and substrings should match (i.e. if "poo" was a keyword, a sentence with "pooface" would match).

[I'm going to spice it up a bit. The keywords are organized by category, one category per row. So if a sentence matches, I'm going to have it create a new sheet for that category. Shouldn't be too hard, but that's not all that important]

But like I said, for 300 or so keywords and 1000 or so sentences it runs like a dog.

One thing I've seen about VBA is that there is approximately 44534543 ways to do any given task, so if you wanna tell me what I did wrong, I'm all ears!

Thanks again!

 

[KLin]

Well you're basically doing 300000 operations involving the instr() function. Plus you're copying data wherever matches are found. It's not very surprising that its slow.

 

[Tweak155]

So yeah I have a couple tips... but still won't be terribly quick, but greatly improved.

You do finalCol = Range("A3").End(xlToRight).Column

This will either:

1: Always give you the same answer
2: Increment by some number you should be able to keep track of.

If you add a column to the right each time, just do finalCol = finalCol + 1. If it never changes, just do that operation outside the loop. The End function is really fast but still should help. It only makes sense to use the End function if your row is changing that you check the columns for.

For example:

For i = 1 to 10
  finalCol = Range("A" & i).End(xlToRight).Column
  ... 'Do some stuff with finalCol
Next i

Next, the copy function is slow. You're better off setting one cell equal to another, and you can do this all in one line:

Sheets("Sheet3").Range("A65536").End(xlUp).Offset(1, 0) = sentence.Value

Also for your inner loop, you should keep track of the row you're printing on rather than FINDING the next row to print on. For example:

Dim currentPrintRow as Long

currentPrintRow = 1
For i = 5 to 15
  Cells(currentPrintRow, 1) = i
  currentPrintRow = currentPrintRow + 1 'This is going to the next row and keeping track
Next i

So if you combine the above 2, you could have something that looks like this:

Sheets("Sheet3").Cells(currentPrintRow, 1) = sentence.Value
currentPrintRow = currentPrintRow + 1

To further speed it up, you can loop through the rows instead of selecting the next offset - set the loop up similar to what you did for the outer loop. Or do something like this (replacing the Do Until IsEmpty loop here...):

ActiveSheet.Cells(4, colInc).Select

searchRow = 0

While ActiveCell.Offset(searchRow, 0).Value <> ""
  ... 'Do some stuff
  searchRow = searchRow + 1
Wend

And my final suggestion is to turn off watching what the program does... this will greatly increase performance...

At the beginning of the sub and JUST before the end... do this:

Sub testLoopsFull()
Application.ScreenUpdating = False

Dim rowInc As Integer
....
...

Next Sentence

Application.ScreenUpdating = True
End Sub

 

[Tweak155]

Well you're basically doing 300000 operations involving the instr() function. Plus you're copying data wherever matches are found. It's not very surprising that its slow.

Way to be helpful, prick. It's not like this is OT. The guy is just learning.

 

[Tweak155]

GREAT tips guys.

Thank you SO much.

Also: I hate VBA. There's just something about the paradigm that doesn't appeal to me. I haven't quite got the hang of what's a range, what's a cell, what can be both, etc.

A cell is always a range but a range isn't always a singular cell. A range can be one cell or multiple cells. For example, Range("A1") = Cells(1, 1). But, Range("A1:A2") is Cells(1,1) and Cells(2, 1). You can also define a range by cells, for example: Range("A1:A2") = Range(Cells(1, 1), Cells(2,1)).

In other words, cells are CONTAINED within a range. A range is an area of cells.

 

[Tweak155]

Without seeing your sheets, this is my stab at it from the code you had written. I put comments in so you can understand even the basics:

Sub testLoopsFull()
    Application.ScreenUpdating = False 'Hide from the user what Excel is doing, greatly increases speed

    Dim colInc As Integer
    Dim finalCol As Integer
    Dim sentence As Range
    
    Dim curReadRow As Long, curPrintRow As Long
    
    'Grab the latest unused row.
    'If the entire column is blank we will start printing on Row 2
    'You can change this...
    curPrintRow = Sheets("Sheet3").Range("A65536").End(xlUp).Row + 1
    
    With Worksheets("Keywords") 'This means anything starting with "." is the same as Worksheets("Keywords").
        finalCol = .Range("A3").End(xlToRight).Column 'Same as Worksheets("Keywords").Range("A3").End(xlToRight).Column because of WITH statement
        For Each sentence In Worksheets("Sheet1").Range("E:E").SpecialCells(xlCellTypeConstants) 'Grabs cells with values only
            For colInc = 1 To finalCol
                curReadRow = 4 'We start reading on row 4, change to correct row as needed
                Do Until IsEmpty(.Cells(curReadRow, colInc)) 'From row 4 until it is blank...
                    If InStr(sentence, .Cells(curReadRow, colInc).Value) > 0 Then 'See if the sentence has this text in it
                        Sheets("Sheet3").Cells(curPrintRow, 1) = sentence.Value 'If it does, print it to sheet 3
                        curPrintRow = curPrintRow + 1 'Move to the next empty row for our next print...
                    End If
                    curReadRow = curReadRow + 1 'Move to the next word to look for
                Loop
            Next colInc
        Next sentence
    End With
    
    Application.ScreenUpdating = True 'Show the user what excel is doing again...
End Sub

 

[scootermaster]

Wow. Thanks for the code. I'm gonna look at it now. That's pretty amazing!

RE: Cells vs. ranges, I got that part, but for example you can say "Range("A1") or something, right? But lets say you have a variable X = 2. You can't very well say "Range(AX)" to get A2, y'know? So using explicit numbers to reference cells vs. using "currentCell"/offsets vs. explicit ranges vs. stuff I don't even understand is confusing.

At these higher levels of abstraction, there's just so many different ways of doing things, it's hard to know which is "best".

But THANK YOU again. I'm certainly learning!

 

[Tweak155]

Wow. Thanks for the code. I'm gonna look at it now. That's pretty amazing!

RE: Cells vs. ranges, I got that part, but for example you can say "Range("A1") or something, right? But lets say you have a variable X = 2. You can't very well say "Range(AX)" to get A2, y'know? So using explicit numbers to reference cells vs. using "currentCell"/offsets vs. explicit ranges vs. stuff I don't even understand is confusing.

At these higher levels of abstraction, there's just so many different ways of doing things, it's hard to know which is "best".

But THANK YOU again. I'm certainly learning!

Dim curRow as Long

For curRow = 1 to 10
  Range("A" & curRow) = "Wow I just wrote to A" & curRow & "!"
Next curRow

EDIT:

In general, Cells will be the fastest, then ever so slightly behind (negligible) Offset, then Range. But for what it's worth I can't remember the last time I used Offset, you can always replace it with Cells, but it can help conceptually write the code.

Cells(2, 1) = Cells(1, 1).Offset(1, 0)

Or better example:

Cells(currentRow + offsetRows, 1) = Cells(currentRow, 1).Offset(offsetRows, 0)

You can convert everything into Cells if need be.

 

[scootermaster]

Dim curRow as Long

For curRow = 1 to 10
  Range("A" & curRow) = "Wow I just wrote to A" & curRow & "!"
Next curRow

EDIT:

In general, Cells will be the fastest, then ever so slightly behind (negligible) Offset, then Range. But for what it's worth I can't remember the last time I used Offset, you can always replace it with Cells, but it can help conceptually write the code.

Cells(2, 1) = Cells(1, 1).Offset(1, 0)

Or better example:

Cells(currentRow + offsetRows, 1) = Cells(currentRow, 1).Offset(offsetRows, 0)

You can convert everything into Cells if need be.

See, I knew I was close!

Your code runs a LOT faster. I've managed to tinker with it a bit...I switched the loops, so it starts looking on sentences first, THEN on keywords (the thought being, once it hits on one keyword, we can move to the next sentence). So I added logic to do that (since there's no "continue" I just used hacky stuff, setting the curReadRow to something ridiculously large, and the colInc to finalCol. That way when I get a hit, it moves to the next sentence).

One thing I can't figure out is before, it copied the whole row, and now it's only copying the sentence itself. I tried to use the "EntireRow" method but it seems odd to paste the entire row in to a cell, given that the line of code in question is:

Sheets("Sheet3").Cells(curPrintRow, 1) = sentence 'If it does, print it to sheet 3

This is where I get confused. Since we're iterating on the column with the sentence specifically, not the row in general, I wonder if this breaks the whole paradigm, or if there's some way at getting the entire row from a cell (which I assume there is) and then pasting it when we have the information we have (which I assume would use curPrintRow).

Edit: See, now I feel like I'll have to loop across each cell, setting each sell in the destination row to the corresponding cell in the source (sentence) row. That seems like a pain. Or, I guess, have an auxiliary "counter" for the outside (sentence) loop that keeps track of the row number, and then use the entireRow thing? Or can you somehow get that information from the "sentence" variable? Both seem sort of hacky. It'd be nice to do something like: target.Row = sentence.entireRow.

/edit

Further edit
I think I got it, using a little google fu. Anyone have a problem with:

 Set wsSource = Sheets("Sheet1")     'Edit "Sheet1" to your source sheet name
 Set wsDestin = Sheets("Sheet3")
 
With wsDestin
          lngDestinRow = .Cells(.Rows.count, "P").End(xlUp).Offset(1, 0).row
          .Cells(lngDestinRow, "A").EntireRow = sentence.EntireRow.Value
End With

Now, I don't get what the .Rows.count, "P".End thing does, exactly. I guess that determines the row we're on? And then the second one says take that row and copy it to my destination?

Yeah, it works, but I kinda have no idea why.

/further edit

 

[Tweak155]

.Rows.Count will just give you the last used row if there are no gaps, "P" is just the column.

I didn't realize you had more data other than the column E. How many columns does the data span? You can always do:

sentence.Value & sentence.Offset(0, 1).Value .... & sentence.Offset(0, n).Value

If it's a bunch of columns, the Entirerow.Value is probably best as you've done above.

 

[scootermaster]

.Rows.Count will just give you the last used row if there are no gaps, "P" is just the column.

I didn't realize you had more data other than the column E. How many columns does the data span? You can always do:

sentence.Value & sentence.Offset(0, 1).Value .... & sentence.Offset(0, n).Value

If it's a bunch of columns, the Entirerow.Value is probably best as you've done above.

Thanks for all the help. That tricky ampersand! Anyway, I finished the script, and, as far as I can tell, it does what it's supposed to/need to.

I don't have any desire to mess with VBA again any time soon.

But I appreciate all the help!