Locating Variable Columns in Excel

[LGMan]

Hi, I'm trying to figure out how to select 3 columns from a worksheet which can have either 3 cols in week1 through to 7 cols in a 5 week month.
Col A will always show Shop, however in week 1 Col B shows Week1 sales and Col C shows the Month sales. In week 2 Col C shows Week2 sales and Month Sales moves to Col D
Regardless of the week number, I only ever need the Shop, latest week and the month sales columns.
Any clues most appreciated

 

[SkipVought]

Hi,
/
What code do you have so far?

You know that your worksheet structure, has you starting with one hand tied behind your back and the other in a sling, making it extremely difficult to make use of Excel's data analysis and data reporting features.

If you were me, I'd put my efforts into NORMALIZING the data, in order to make the analysis possible. Your answer, with a properly structured table, can be solved with spreadsheet formulas quite simply, in just a few minutes.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

The data that I'm after is from a web report that is available on broadcast service as an xls file. As it only ever going to hold between 3 and 6 columns, I can get by using

If Range("C1") = "Month" Then
    Range("A:A,B:B,C:C").Copy
    ElseIf Range("D1") = "Month" Then
    Range("A:A,C:C,D:D").Copy
    ElseIf Range("E1") = "Month" Then
    Range("A:A,D:D,E:E").Copy
    ElseIf Range("F1") = "Month" Then
    Range("A:A,E:E,F:F").Select
    End If

However I'd like to just copy the rows that hold data in those 3 columns rather than the entire column. This is where I don't have a clue how to make happen

 

[SkipVought]

dim rMO as range, rCopy as range
with activesheet
  set rmo = .rows(1).find("Month")
  with .usedrange
    intersect(range(cells(1"A"), cells(.rows.count,"A")), _
      range(cells(1,rmo.column), cells(.rows.count, rmo.column)), _
      range(cells(1,rmo.column+1), cells(.rows.count, rmo.column+1))).Copy
  end with
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops, I make a fatal mistake...

dim rMO as range
with activesheet
  set rmo = .rows(1).find("Month")
  with .usedrange
    UNION(range(cells(1"A"), cells(.rows.count,"A")), _
      range(cells(1,rmo.column), cells(.rows.count, rmo.column)), _
      range(cells(1,rmo.column+1), cells(.rows.count, rmo.column+1))).Copy
  end with
end with

On second thought, I'd copy the column A range first and then the UNION of the next two thereafter.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}