Deleting Columns based on their name

[MrBen24]

I do some work for a car racing team, we generate a whole lot of data each lap our racing car completes and I use (try to anyway) Excel to help me highlight when certain parameters go out of range.

I would like to be able to copy some data from a table and then use a Macro in a different spreadsheet that will:

- Paste Special . Values all the data (on the new sheet)

- Delete 1 column which is always called 'Out Lap' (1st cell of the column is called 'Out Lap') and one column which is always called 'In Lap'
The number of columns changes all the time, depending on how many laps the race car has travelled, but I always end up with 1 called 'Out Lap' and 1 called 'In Lap' which I want to remove.

- Finally I want to select all the remaining data and copy it so it is ready to be pasted into a different sheet.

Your help would be much appreciated, I have been trying very hard to find help using existing threads but my Excel knowledge is too basic...
Thanks,
MrBen24

 

[Smitty]

Welcome to the Board!

I'd start by recording a macro doing what you want, then paste the resulting code back here and someone can set it up dynamically for you.

 

[MrBen24]

Hi Smitty,

Good suggestion, I can do that:



Sub Data_Sort_Compare()
'
' Data_Sort_Compare Macro
' Macro recorded 03/05/2011 by bens
'
'
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("R:R").Select
Selection.Delete Shift:=xlToLeft
Range("B1:Q88").Select
Selection.Copy
End Sub



My problem is that the Columns I want to delete are always different in location (not necessarily B:B and R:R) but always called 'Out Lap' and 'In Lap'.
Also the final selection is here only for the range in this particular example, but I would like it to be for all the data on the sheet.

Thanks,
MrBen24

 

[Smitty]

You can use the Find method to find "Out Lap" & In Lap", or use the Match method:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        i = Application.WorksheetFunction.Match("Out Lap", Rows("1:1"), 0)<br>            Cells(1, i).EntireColumn.Delete<br>        i = Application.WorksheetFunction.Match("In Lap", Rows("1:1"), 0)<br>            Cells(1, i).EntireColumn.Delete<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

As for the variable range to copy, you can use: Cells.Copy, which will copy the entire sheet, or ActiveSheet.UsedRange.Copy, which will only copy what Excel sees as having been used.

Also note that it's rarely necessary to select objects in order to work with them, so when you see "Select" followed by "Selection" you can eliminate both statements and concatenate the remaining code:

Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

HTH,

 

[MrBen24]

Thanks,
the only thing is the paste special bit does not work, I keep getting the same error message when I run the macro:
PasteSpecial method of Range class failed
and from what I can tell, it is because I copy some data from a different sheet, then try to run the macro, but Excel automatically clears the Clipboard when it starts a macro. Is this correct? Is there a way around it?

Also, the other thing I would like to do is clear some cells from a given column; so for example, in the column called Lap1, clear the cell on row 6, row 8 and row 22.

Do you know how I can achieve this?

Thanks again for your help,
Ben

 

[Smitty]

This works fine:

<font face=Calibri>    Sheets("Sheet1").Range("A1").Copy<br>    Sheets("Sheet2").Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<br>        :=False, Transpose:=False</FONT>

Excel won't clear the clipboard until you tell it to do so, or if you replace what's there with something else.

As for clearing certain cells in a column, you can adapt what I posted earlier to find that column, then just use: Cells(6, i).EntireColumn.Delete

 

[zakasnak]

Smitty,

Can you help me modify this code to look for the tab name in row $1:$1, beginning in Column H & delete all columns that do not contain the tab name after column G.

[COLOR=#00007f]Sub[/COLOR] Books_Title_Draw()[/FONT]
[FONT=Microsoft Sans Serif][COLOR=#00007f]Dim[/COLOR] i [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]Long[/COLOR][/FONT]
[FONT=Microsoft Sans Serif]     i = Application.WorksheetFunction.Match("Out Lap", Rows("1:1"), 0)[/FONT]
[FONT=Microsoft Sans Serif]         Cells(1, i).EntireColumn.Delete[/FONT]
[FONT=Microsoft Sans Serif]     i = Application.WorksheetFunction.Match("In Lap", Rows("1:1"), 0)[/FONT]
[FONT=Microsoft Sans Serif]         Cells(1, i).EntireColumn.Delete[/FONT]
[FONT=Microsoft Sans Serif][COLOR=#00007f]End[/COLOR] [COLOR=#00007f]Sub[/COLOR][/FONT]

 

[Smitty]

You can get the variable range starting at H with something like this:

<font face=Calibri>    <SPAN style="color:#00007F">Dim</SPAN> LC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        LC = Cells(1, Columns.Count).End(xlToLeft).Column<br>            Range([H1], Cells(1, LC)).Select</FONT>

HTH,