PDA

View Full Version : [SOLVED] Delete Columns based on the font colour in row 1



pcarmour
11-25-2015, 08:28 AM
Hi,
I have a spreadsheet with dates in cells F1 to Z1.
With Conditional formatting I have highlighted the weekend dates with a red font and yellow fill. I now need to delete these entire columns.
Of course there may be an easier way to just delete columns with a weekend date.

Any Help would be very much appreciated.

I am working with Windows Home Premium version 6.1.7601 SP 1 Build 7601and Excel version 14.0.6123.5001 (32 bit)

mancubus
11-25-2015, 08:56 AM
hi.
try with a test file

the procedure tests if the dates in Z1 to F1 falls to weekend.

vbMonday parameter in weekday function sets Monday as first day of the week. So Saturday is 6 and Sunday is 7.



Sub vbax_54369_Del_Weekend_Cols()

Dim i As Long

With Worksheets("MySheet") 'Change MySheet to suit
For i = 21 To 6 Step -1
If Weekday(.Cells(1, i), vbMonday) > 5 Then .Columns(i).Delete
Next i
End With

End Sub

pcarmour
11-25-2015, 09:20 AM
Hi Mancubus,
Thank you for your very prompt reply and your code which works brilliantly.

SamT
11-25-2015, 09:40 AM
A little more Generic is
For i = Cells(1, Columns.Count).End(xlToLeft).Column To 6 Step -1

pcarmour
11-25-2015, 12:33 PM
Hi SamT,

Thank you I have added your variation and all works very well.

mancubus
11-26-2015, 02:43 AM
@SamT

the OP seemed confident about ranges. :devil2:

SamT
11-26-2015, 05:17 AM
I know. I probably would have left it if it was my workbook. I just wanted to introduce the concept of Range.End. I was surprised that you didn't.

mancubus
11-26-2015, 06:05 AM
i was about to do so...
but i frequently work with tables with static number of columns...
in some cases, where i exactly know the numbers of columns, i tend to hardcode them...