Posts by Matteous

Re: Select Case Syntax

I'm still getting a Runtime 1004 Error on line 37

.Range(Cells(1, icolstep).Address & ":" & Cells(1, icolstep + 1).Address).EntireColumn.Hidden = False

Re: Select Case Syntax

Hi Attila,
I've used 44 to 182 as a worst case scenario of how many tasks someone would want in their project. Most of the time it will
be between 5-15 tasks, but we have a couple of projects with nearly 70 tasks.
Ultimately what I'd like to do is work out how to automatically add a column when a new task is required, but that's way beyond my capabilities at this stage.

I'll test it out and let you know.
Thankyou once again!

Re: Select Case Syntax

Can anyone help with this please? Sorry I'm not sure where I need make the change, I'm still learning VB...
Thankyou all : )

Re: Select Case Syntax

The last cell is row 182, and I'm not sure where you mean to change from an integer to a long?
Do you have a copy of the sheet I uploaded in a separate post?

Re: Select Case Syntax

Something's still not quite right sorry... I'm getting a Runtime Error 1004 on line 37?
Thankyou for all your help!

Re: Select Case Syntax

Hi Attila,
I'm getting an "Invalid Qualifier" error on xlCellTypeLastCell (Ln 33 Col 43)?
And thankyou that looks a LOT better! Much easier to read!
Thanks,
Matt

Re: Select Case Syntax

Also, just to confirm, my intent is to hide columns "E:F" if there is no value in cell B44, hide "G:H" if B45 is blank etc etc. Will this code achieve this?
My apologies if I'm asking silly questions, I've only been using VB for a matter of weeks :confused:

Re: Select Case Syntax

Hi Attila,
First I was getting a Runtime 1004 'Range of object '_Global' error. I then changed (Range(B & i) to (Range("B" & i) and did the same for Range(Cells("1", etc
but now I'm getting a Runtime 1004 Application-defined or object defined error.

Sub RemoveBlankColumns()
Dim i, icolstep As Integer
Dim ws As Worksheet
 
     With Application
                .ScreenUpdating = False
                .Calculation = xlCalculationManual
     End With
 
For Each ws In ActiveWorkbook.Sheets
 
     If ws.Tab.ColorIndex = 35 Or ws.Tab.ColorIndex = 50 Or ws.Tab.ColorIndex = 10 Or ws.Tab.ColorIndex = 51 Then
 
         icolstep = 5 'Column variable
 
     For i = 44 To 182 'Row variable
              If IsEmpty(Range("B" & i)) Then
                   Range(Cells("1", icolstep), Cells("1", icolstep + 1)).EntireColumn.Hidden = True
              Else
                   Range(Cells("1", icolstep), Cells("1", icolstep + 1)).EntireColumn.Hidden = False
              End If
              icolstep = icolstep + 2
     Next i
 
     End If
 
Next ws
 
     With Application
                .ScreenUpdating = True
                .Calculation = xlCalculationAutomatic
     End With
 
End Sub

I appreciate your help : )

Hi everyone,
I'm looking to make my code more efficient by changing my If Then statements to Select Case but I'm having trouble.
I've read through numerous guides and searched the forums for advice on it's syntax but I can't wrap my head around it.
Basically, if a certain cell has a blank value, I need a corresponding column to be be hidden.
My code is as follows;

Sub RemoveBlankColumns()
For Each ws In ActiveWorkbook.Sheets
ws.Activate
If ws.Tab.ColorIndex = 35 Or ws.Tab.ColorIndex = 50 Or ws.Tab.ColorIndex = 10 Or ws.Tab.ColorIndex = 51 Then
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If Range("B44").Value = "" Then
Range("E:F").EntireColumn.Hidden = True
Else
Range("E:F").EntireColumn.Hidden = False
End If
If Range("B46").Value = "" Then
Range("G:H").EntireColumn.Hidden = True
Else
Range("G:H").EntireColumn.Hidden = False
End If
'Continues on until "EK:EL"

Much appreciated everyone :smile:

Re: Replicate column formats on multiple sheets

Converteds - Please let me know where you live and I'll send you a box of chocolates or something!!! Thankyou so much!!!

Thankyou everyone for all your help!! This forum is a lifesaver!

Re: Replicate column formats on multiple sheets

forum.ozgrid.com/index.php?attachment/38614/

The working code is "Sub HideBlanks ()" in worksheet "Oct 2011".

I need to apply this to the other tabs in the worksheet, but I need to exclude certain worksheets (that I haven't attached due to size restrictions).

Please help, I'm losing sleep (and hair!) over this!

Re: Replicate column formats on multiple sheets

Hi again,
I'm going insane trying to get this work!
I'll try to explain what I'm trying to do again...

I have one worksheet for each month of the year (names of which can change depending on the project start date).
I've managed to get it so the unrequired columns are hidden automatically in the first worksheet, but I need each
worksheet to have the same columns hidden/showing. The worksheet I need to copy is 'Sheet7', and it needs copying
to 'Sheet9' through 'Sheet19'.

If you would like me to upload the s/sheet please let me know, and I really appreciate your help!

Re: Replicate column formats on multiple sheets

Thanks Mike, I'm getting very close! But it doesn't seem to copy all the formatting, it leaves out the merged cells, leaving them all split up on each sheet.
Any ideas?

Re: Replicate column formats on multiple sheets

Hi Junho,
Am I correct in thinking that will replicate the formatting in 'Sheet 4' and apply it to sheet 1, 2 & 3?

And Hi MikeRickson,
Putting the 'Rem' in front of my macro makes the string green, which as I understand means it's not a part of the calculation (like a comment).
Is this correct?

Thankyou all for your help!

Re: Replicate column formats on multiple sheets

Hi Junho,
Thankyou for your reply!
Sorry I should have mentioned that I don't want to apply the macro to all worksheets, just certain ones.

Also, I've been told that using "Select Case" statements would be more efficient than using "If Else" statements, but I don't know how I'd put my code into a "Select Case" type statement?

Thanks again,
Matt

Hi everyone,
I'm trying to hide columns depending on whether one of the cells is blank or not, and I need to apply this to several sheets, all of which have the same layout and formatting.

I'm very much a beginner with VBA, so if anyone can suggest a better way of going about it I would really appreciate it.

Private Sub CommandButton1_Click()
If Range("B44").Value = "" Then
Columns(5).Hidden = True
Columns(6).Hidden = True
Else
Columns(5).Hidden = False
Columns(6).Hidden = False
End If
If Range("B46").Value = "" Then
Columns(7).Hidden = True
Columns(8).Hidden = True
Else
Columns(7).Hidden = False
Columns(8).Hidden = False
End If
If Range("B48").Value = "" Then
Columns(9).Hidden = True
Columns(10).Hidden = True
Else
Columns(9).Hidden = False
Columns(10).Hidden = False
End If
If Range("B50").Value = "" Then
Columns(11).Hidden = True
Columns(12).Hidden = True
Else
Columns(11).Hidden = False
Columns(12).Hidden = False
End If


'copies the column widths and their formats
Sheets("Sheet7").Range("E:BT").Copy
Sheets("Sheet9").Range("A:C").Paste = xlColumnWidths
End Sub

Thankyou all,
Matt