Need help: delete colums

[Hans_]

Hi all,

I have a problem and don't know how to fix it.
I will explain what i want to do with the macro.

I have a form where users can fill in a beginweek and an endweek.
Now i want excel to place the beginweek in cell B10 and the next week in cell C10 and so on. After excel did that i want excel to delete all the columns behind the endweek.

Example: beginweek is week 10 and endweek is week 21. So cell B10 should be 10, cell C10 should be 11, cell C12 should be 12 and so on till cell M10 is 21.
Excel then should delete every column behind column M, so from column N.

Is this possible with a macro??

Then, is it also possible when i have a beginweek of week 48 and it ends in week 5 of the next year...to have it automatically, after week 52 or 53, start with week 1? I hope this is possible.

Many thanks in advance!!!

Greets Hans

[Tom Ogilvy]

Dim b as Long, c as Long
Dim i as Long
b = clng(Userform2.BeginWeek.Text)
c = clng(Userform2.EndWeek.Text)

i = 0
do
cells(10,2 + i).Value = b
b = b + 1
if b > 52 then b = 1
i = i + 1
loop until b = c
set rng = Range(cells(10,2+i),cells(10,256))
rng.Entirecolumn.Delete

Test this on a copy of your worksheet.

--
Regards,
Tom Ogilvy

"Hans_" <[email protected]> wrote in
message news:[email protected]...
>
> Hi all,
>
> I have a problem and don't know how to fix it.
> I will explain what i want to do with the macro.
>
> I have a form where users can fill in a beginweek and an endweek.
> Now i want excel to place the beginweek in cell B10 and the next week
> in cell C10 and so on. After excel did that i want excel to delete all
> the columns behind the endweek.
>
> Example: beginweek is week 10 and endweek is week 21. So cell B10
> should be 10, cell C10 should be 11, cell C12 should be 12 and so on
> till cell M10 is 21.
> Excel then should delete every column behind column M, so from column
> N.
>
> Is this possible with a macro??
>
> Then, is it also possible when i have a beginweek of week 48 and it
> ends in week 5 of the next year...to have it automatically, after week
> 52 or 53, start with week 1? I hope this is possible.
>
> Many thanks in advance!!!
>
> Greets Hans
>
>
> --
> Hans_
> ------------------------------------------------------------------------
> Hans_'s Profile:
http://www.excelforum.com/member.php...o&userid=25618
> View this thread: http://www.excelforum.com/showthread...hreadid=390317
>

[Hans_]

Thnx Tom, it works almost perfect!!

But, when i fill in an EndWeek of 48 it deletes from week 48, so the last week i see is week 47.

Something you know i can do about that?

[Tom Ogilvy]

Dim b as Long, c as Long
Dim i as Long
b = clng(Userform2.BeginWeek.Text)
c = clng(Userform2.EndWeek.Text)

i = 0
do
cells(10,2 + i).Value = b
b = b + 1
if b > 52 then b = 1
i = i + 1
loop until b = c
' change 2 to 3 then
set rng = Range(cells(10,3+i),cells(10,256))
rng.Entirecolumn.Delete

--
Regards,
Tom Ogilvy

"Hans_" <[email protected]> wrote in
message news:[email protected]...
>
> Thnx Tom, it works almost perfect!!
>
> But, when i fill in an EndWeek of 48 it deletes from week 48, so the
> last week i see is week 47.
>
> Something you know i can do about that?
>
>
> --
> Hans_
> ------------------------------------------------------------------------
> Hans_'s Profile:
http://www.excelforum.com/member.php...o&userid=25618
> View this thread: http://www.excelforum.com/showthread...hreadid=390317
>

[Hans_]

Hi Tom,

No, this didn't work either. Now i didn't see a number in the last cell, so i didn't see EndWeek.

But i found out how it works:

Dim b As Long, c As Long
Dim i As Long
b = CLng(UserForm1.TextBox15.Text)
c = CLng(UserForm1.TextBox16.Text)

i = 0
Do
Cells(10, 2 + i).Value = "Week " & b
b = b + 1
If b > 52 Then b = 1
i = i + 1
Loop Until b = c + 1
Set rng = Range(Cells(10, 2 + i), Cells(10, 256))
rng.EntireColumn.Delete

So, at Loop Until i said b= c + 1. That works perfect!!

Thanks for your help!!