0

I've spent hours on the internet trying to work something out but can not get a vba fix to work on a complex sheet like mine! Your help would be appreciated.

For all these queries I'd like to loop through all sheets, but skip the sheets named "timekeeper code", "bill date" & "summary" - these names will always be the same but I can not name the sheets. The script needs to loop as the names and quantity will vary.

On all other sheets it will loop I need to select the range "A1002:A2003" and if there is a duplicate delete the row.

I also need to do the same for range "A2005:A3006".

Please note that both of these ranges are with in tables but again the table names can't be named as they will vary.

I'm not sure if it helps but the full table range for A1002:A2003 is A1002:B2003 and the full table range for A2005:A3006 is A2005:AD3006

I can also not go from A1002 straight to A3006 as some values will be shown in both ranges but I will need them both

looping the same sheets, I then need to delete all rows from A1001 upwards to the last used cell where the cell value is = ""

I have a very limited skill on VBA and quite a complex query so I'm basically stuck starting from scratch so any code you have to do this would be amazing!

I hope this makes sense.

Many thnaks

Maximilian Peters
  • 30,348
  • 12
  • 86
  • 99
Fcarboni
  • 25
  • 6
  • 3
    `spent hours on the internet trying to work something out` so show what you have so far. `any code you have to do this` SO isn't a code writing service. – findwindow May 23 '16 at 19:21
  • So you have a query? Can you just filter them out before loading into Excel? And @findwindow is right, this isn't a code writing service. You have to try some VBA yourself and then come to us with what you have tried. – Michael Z. May 23 '16 at 20:47

1 Answers1

0

First google entry solves your problem:

Delete all duplicate rows Excel vba

now you have to generate a loop around this function to target the correct sheets.

Community
  • 1
  • 1
Heini
  • 41
  • 7
  • I tried that on one sheet and all it did was jump to that sheet and select the range. `Private Sub CommandButton1_Click()` `Sheet18.Activate` `With ActiveSheet` `Set rng = Range("A1002", Range("B1002").End(xlDown))` `rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes` `End With` `End Sub` – Fcarboni May 24 '16 at 09:46
  • Columns:=Array(1,2) deletes the row if the entries in AxBx are equal to the entries in AyBy – Heini May 24 '16 at 10:30
  • Columns:=Array(2) checks only for the second Column and delets the whole line. It is difficult to set up a macro if I don't know how the input looks like – Heini May 24 '16 at 10:31