Excel VBA code to turn a row with a variable number of columns into fixed-length rows

microsoft excelmicrosoft-excel-2013vba

I would like to change this:

Filer   ShareName       IPAddress1    IPpaddres2    IPAddress2    ... ... ....
ServerA /share1/tools   192.168.1.52  192.168.1.65  192.168.1.158
ServerA /share/library  192.168.1.65  192.168.1.61  192.168.1.155
ServerB /share/tools    192.168.1.158 192.168.1.159
ServerD /share/misc     192.168.1.7
..
...
.....
.......

To this, in a new worksheet:

Filer   ShareName       IPAddress
ServerA /share1/tools   192.168.1.52
ServerA /share1/tools   192.168.1.65
ServerA /share1/tools   192.168.1.158
ServerA /share/library  192.168.1.65 
ServerA /share/library  192.168.1.61 
ServerA /share/library  192.168.1.155
ServerB ... ...
ServerD ... ...
..
...
.....
.......

Best Answer

  1. Go to the developer tab and click "record macro"
  2. Then add a new sheet
  3. Stop he recording
  4. look at the code generated by clicking "Visual Basic" it is probably in "Module 1"

Repeat for next problem and use this code as a base for what you are trying to build.

When you get stuck, Google your problem. If that doesn't solve it, post the code you have here and specify exactly what you want it to do, what you think it does and what you think is wrong or don't understand.

Related Question