Selection from cell to certain number/date in that row

Hi all,

I have an excel sheet with text in column A-D and after that in row 5, from E5 to AI5, numbers 1 to 31.
These numbers represent the day of the month but are formatted as [I]General[I], so no date format.

How can I delete all columns from column C to the column with the day of the month of today using VBA?

This is part of a more complex macro, but I don't know yet how to make a selection like this.

Any help please?

Thanks!

Hi Kreppie can you provide your sheet as example as well?

Voorbeeld 2 July.xlsx

Hi,

Will this suffice?

So for today (22/07) I want to delete columns C:Z,

but tomorrow, it should be columns C: AA.

voorbeeld-2-july-updated.xlsHi Kreppie,

You can find some code below.
I have attached your updated file as well.

So the function / macro "deleteColumns" will delete the columns, you cannot restore that. So in case you have the next month, you need to update your sheet again.
Maybe more convienent to just hide/unhide the columns? Then you can use the other macro "hideUnhideColumns"

If you check my returned workbook, the "thisWorkbook" object refers to this macro on startup. With other words: If you open your file, it checks what columns need to be displayed.

Problem solved?

Sub deleteColumns()

Dim r As Range
Dim rFound As Range
Dim d As String


d = Day(Date) 'dateformat

Set r = ActiveSheet.Range("E5:AI5")

'Find the column with the day number
Set rFound = r.Find(what:=d, lookat:=xlWhole)

'Protect in case something is already deleted
If rFound Is Nothing Then
    MsgBox "Day not found, or already deleted?"
    Exit Sub
End If

'Delete from column D until the column found with the day number (minus offset 1 day/column)
ActiveSheet.Range("D1:" & rFound.Offset(0, -1).Address).EntireColumn.Delete

'Deselect
ActiveSheet.Range("A1").Select
End Sub
Sub hideUnhideColumns()

Dim r As Range
Dim rFound As Range
Dim d As String


d = Day(Date) 'dateformat

Set r = ActiveSheet.Range("D5:AI5")

'activate all columns first
r.Columns.EntireColumn.Hidden = False

'Find the column with the day number
Set rFound = r.Find(what:=d, lookat:=xlWhole)

'Protect in case something is already deleted
If rFound Is Nothing Then
    MsgBox "Day not found, or is it deleted?"
    Exit Sub
End If

'Delete from column D until the column found with the day number (minus offset 1 day/column)
ActiveSheet.Range("D1:" & rFound.Offset(0, -1).Address).EntireColumn.Hidden = True

'Deselect
ActiveSheet.Range("A1").Select
End Sub

Hi thelongpants,

Thanks so much for the help.
The hide-macro works perfect and is more suitable indeed.

But the 'problem' is not solved intirely
I had cut the problem in pieces a bit to adjust and build my own bigger macro.
So I don't need the day of today but of tomorrow and day after that.
This was easy by just adding a '+1' to the r.Find.

There is next step, maybe more complex:

I only want to see the rows with NPU in the column with day of tomorrow and/or column with day after that.

And if possible, even sorted by NPU in the first column and formatstyle Bad, and blanks in first column and NPU in second column, formatstyle good

I've added another attachment with the final outcome how it should look like.

Voorbeeld 2 July.xlsm

The comments with every part really helps by the way for understanding!

voorbeeld-2-july v2.xlsm

Hi Kreppie,

Interesting :-)
Updated according to you specs. Please have a look whether ok.
I used witin the macro another function to update the rows. (this makes the initial macro not changed, and you are able to change this function, without changing something else), ... can be convenient.

Let me know your next step :-)