reformat VBA

[cmccabe]

I am trying to reformat a worksheet and am having trouble with a VBA to do this. The 5 steps are below and an example is attached.

Also, the rows #'s for columns can be variable but the column headers are always the same.

For example, in the attached there are 183 rows, but in the next there might be 200 rows to reformat. Thank you very much.

Step 1: Rename column
AK – Index
AL – deleted
AM – Gene
AN – mRNA
AO and AP (deleted)
AQ – Coverage
AR – Score
AS – A(#F,#R)
AT – C(#F,#R)
AU – G(#F,#R)
AV – T(#F,#R)
AW – Ins(#F,#R)
AX - Del(#F,#R)
AY- SNP
AZ – Mutation
BA – Frequency
BB – AminoAcid

Step 2: move renamed columns
Start at column A, Row 4

Step 3: delete columns AP (Otherinfo) to BQ (Amino Acid)

Step 4: Rename column
AP – Homopolymer
AQ – CDS
AR – Splice
AS – Strand Bias
AT – Classification
AU- HGMD
AV – Disease
AW – References
AX – Sanger

Step 5: Insert column header in A1 and B1 and highlight cells yellow in A1,A2,B1,B2
A1 – Name
B1 - Gender
A2 – “leave blank”
B2- “leave blank”
A3 – “leave blank”
B4 – “leave blank”

[Norie]

Are you starting by deleting columns A to AJ?

[cmccabe]

No, those columns are just moved to the right to make room for the inserted columns.

I forgot a step as well, can a column "Inheritance" be added to C4? I updated the workbook with the change as well. Thanks

[Norie]

Perhaps.

Please Login or Register  to view this content.

[cmccabe]

Thank you very much, the code works great.

[cmccabe]

Since my data is dependent on the test that is run is it possible to not use a column but a range instead? For example, in the attached data there are examples of the data in tab One and Two. In workbook Two there are 3 additional columns. I'm not sure the best way to reformat the data.

The code from post 1 (classify) works great for the first condition, but since there are additional columns in the second condition it does not.

Column AK in One starts the code
Column AL in Two stars the code

Any ideas ? Thanks.