0

I have a csv file I have recorded a macro for to manipulate the data of the CSV file as I need it. Part of the macro looks for a cell containing the text "month index". Once this cell is located, a range is selected (A9,B50) for example. The problem is, the "month index" cell can be located in A7, A8, A11 etc so the macro pulls the wrong data if the cell reference is difference. The range I select is consistent once "month index" is found so I think I can use the offset instruction in someway but not sure.

Currently my Macro that handles this bit looks like this: 

        Range("A1").Select     
        Cells.Find(What:="month index", After:=ActiveCell, LookIn:=xlFormulas, _    
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _        

MatchCase:=False, SearchFormat:=False).Activate     
        Range("A9:B9").Select     
        Range(Selection, Selection.End(xlDown)).Select     
        Selection.Cut
  • Can anyone help me figure this out?
vacip
  • 5,246
  • 2
  • 26
  • 54
Superhans
  • 121
  • 3
  • 13
  • Just remove the line `Range("A9:B9").Select`. You can use F8 to step through the code. Have Excel and VBE side by side, then you can see which line of code does what. – vacip Jan 03 '17 at 12:20
  • can "month index" be anywhere in worksheet ? or is it restricted to a certain area ? can it appear a few times in a worksheet ? – Shai Rado Jan 03 '17 at 12:20
  • why use the recorder? It is always inefficient, buggy, impossible to debug... – Pierre Jan 03 '17 at 12:49

1 Answers1

0

Using the Macro Recorder is great to get started on a task, however it adds a ton of extra code that is inefficient. Learn how to Avoid Select/Activate.

You need to grab the row that 'Month index" appears in, since it is dynamic and moves, you can't just select the same range all the time, it needs to move based on the row found...try something along these lines.

Dim iRow as Integer, iMax as Integer

iRow = Cells.Find(What:="month index", After:=ActiveCell, LookIn:=xlFormulas, _    
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _        
    MatchCase:=False, SearchFormat:=False).Row

iMax = Cells(iRow, "A").End(XlDown).Row
Range("A" & iRow & ":B" & iMax).Cut
Community
  • 1
  • 1
Rdster
  • 1,846
  • 1
  • 16
  • 30
  • You Sir are a Genius - Thank you ever so much, that worked a treat! Can you guide me in the direction of learning more about what the Dim iRow as Integer etc. commands are doing? I literally copied and pasted your code and it works but would love to know what it is actually doing! – Superhans Jan 03 '17 at 20:35
  • Dim statements declare a variable a certain data type. Integer is one type. Check [this](http://www.quepublishing.com/articles/article.aspx?p=339929&seqNum=2) out for more info on data types. A great resource for those new to VBA is [Excel VBA Intro](https://www.youtube.com/playlist?list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5). – Rdster Jan 03 '17 at 20:44