Row/Range maniplulation 1

[ChrisBurch]

Hi all,

I'm new to Excel vba, and need advice to manipulate a spreadsheet that I've dumped out from another program (7000+ lines).

The problem:-

Starting from row 1, look down column F until the word 'gst' is found. Select this row plus the previous 4 (5 in total).

Append these rows (5) to another sheet, then delete them from the original sheet. Repeat for the each occurence of 'gst' until the end of the sheet.

Many thanks in advance. Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[logius]

Hmm.. Try this code out. I'm writing this off the top of my head, so it might not work perfect.

Sub DoWhatITellYA()
Dim TestRange As Range
Dim rngSelect As Range
Dim MySheet As WorkSheet

  Set MySheet = ActiveSheet

While Not (EOF)
  MySheet.Range("F1").Activate

  'Select data according to word "gst"
  Set TestRange = MySheet.Cells.Find(What:="*Count*", _
                         After:=ActiveCell, _
                         LookIn:=xlValues, _
                         LookAt:=xlWhole, _
                         SearchOrder:=xlByRows, _
                         SearchDirection:=xlNext, _
                         MatchCase:=False)
  If TestRange Is Nothing Then
    MsgBox "Error: Data not found!", vbCritical
  Else
    Set rngSelect = Union(TestRange, _
                          TestRange.Offset(-4, 0))
    rngSelect.Copy(ActiveWorkbook.Sheets(2))
    rngSelect.Delete
  End If
Wend
End Sub

Just from looking at it, I know that the entire row won't be selected, only the individual cells. I'll leave it to you to try and solve this issue. Try this webpage to give you ideas on how to work with ranges.

http://www.microsoftexceltraining.com/VBA/ExcelRanges.htm

Remember, if you get stuck on syntax in the code, then click on the item and hit F1 to see a description in the help file. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[ChrisBurch]

Thanks Logius,

I did manager to cobble some code together that did the job, even if it did cause an error at the end. I suspect that my loop exit didn't work, and I'll try to fix it for the experience (any suggestions welcome).

Sub Deletegst()
'
' Deletegst Macro
'

Dim Check, count
Check = 1: count = 0

While Check < 4988
Range(&quot;F1&quot.Select
Cells.Find(What:=&quot;gst&quot;, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Check = ActiveCell.Row 'I think this is the problem
ActiveCell.Offset(-4, 0).Select
ActiveCell.Rows(&quot;1:5&quot.EntireRow.Select
Selection.Delete shift:=xlUp
count = count + 1
Wend

Debug.Print count
End Sub

Thanks for the assistance, Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[ChrisBurch]

Logius,

Came back and gave you a star after checking out the link.

Thanks, Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[RobBroekhuis]

Chris,
I usually prefer not to use VBA code that changes the active cell - it's cleaner to use variables. This piece of code from one of my apps should help demonstrate one way of doing this. I think your problem comes from trying to FIND beyond the last occurrence of your string - the code below addresses this (by checking for IS NOTHING):

  dim c as range
  With Worksheets(sheetnr).UsedRange
       lastrow = 0
       Set c = .Find(findterm, LookIn:=xlValues)
       If Not c Is Nothing Then
           firstaddress = c.Address
           Do
               '(process cell c here)
               Set c = .FindNext(c)    
           Loop While Not c Is Nothing And c.Address <> firstaddress
        End If
  End With

Cheers
Rob

 

[logius]

Chris,
I think Rob's right. You'll probably get an error if you try and run the find when there isn't an item to be found. Dunno why they coded it like this, but I've run into it before. That's what I get for writing the Sub in 10 minutes, though. Also, VBA can be a real headache sometimes, and it's usually something simple and goofy that fuddles the code up.

Rob,
Pretty cool code. Never thought of doing it that way. Always glad to get help from a fellow Rob.

- Rob J ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[ChrisBurch]

Thanks guys, I dont't understand the code...but I'm working on it.

Through these forums, and books, I'm now starting to do okay with Access vba (my opinion X-)), and I'll get there with excel too.

Any reccomendations re books? Chris

It worked yesterday.
It doesn't work today.
That's Windows!