Sleeper: Minimizing code

[juster21]

what does this code mean?



ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1


thanks!!!

[Zack Barresse]

Hi jester21, welcome to the board!! :006:

That is just saying to scroll the active window (which ever window has the focus, or is activated) a set amount of times. In this case, scrolling across (columns) as you could with the scroll bars a set amount of times. If you were to add all those numbers up, you could use just one line with that total number of scroll (values).

In all actuality, there isn't really a whole lot of need to scroll the window. Generally it's been generated by the Macro Recorder when a user scrolled while recording and has been left in the code or modified slightly.

[juster21]

i thought that the code was useless!!! this is from a macro recorder and i'm trying to clean it up a bit so that only the useful stuff remains and then try and take the recorder code and make it what I want...

[juster21]

is there anyway to minimize this code?



Range("BG20:BM20").Select
Selection.Font.Bold = True
Selection.Merge

Range("BN20:BT20").Select
Selection.Font.Bold = True
Selection.Merge

Range("BU20:CA20").Select
Selection.Font.Bold = True
Selection.Merge

Range("CB20:CH20").Select
Selection.Font.Bold = True
Selection.Merge


:dunno

[Jacob Hilderbrand]

Try this:



Dim i As Long
For i = 59 To 86 Step 7
With Range(Cells(20, i), Cells(20, i + 6))
.Font.Bold = True
.Merge
End With
Next i

[juster21]

thanks jake but can you explain what this is doing?
i may be able to use this with other parts of this job....thanks!!!

[Jacob Hilderbrand]

For i = 59 To 86 Step 7

i will go from 59 to 86 skipping 7 each time.



Range(Cells(20, i), Cells(20, i + 6)


Cells specifies the cell address by Row, then Column. So I am specifying the range starting at (20,59) (BG20) and ending at (20,65) (BM20). This is the first range to process.

The next iteration of the loop, the columns will be increased by 7, so we will have the next range to work with.

[juster21]

in the first cell of each range I want to add the name of each month. how can I set the months so that they are selected too? for instance, C3 = Jan, J3 = Feb, Q3 = March.
i'm thinking that using your example to do this will further reduce the code.

[Zack Barresse]

juster21, I merged your two threads, as they are relevant to each other. Hope you don't mind. :)

[Desert Piranha]

Try this:


Dim i As Long
For i = 59 To 86 Step 7
With Range(Cells(20, i), Cells(20, i + 6))
.Font.Bold = True
.Merge
End With
Next i

Hi,
I been trying to incorporate this sample of "For i" above, without the "Step", to consolidate the sample code below. No sucess. Will a variation of that work? Or another way would be better? Thx for any feed back.


Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("a1").Value <= 1 Then
Range("b1") = ""
Else:
Range("b1").Value = Range("A1") & "MyText"
If Range("a2").Value <= 1 Then
Range("b2") = ""
Else:
Range("b2").Value = Range("A2") & "MyText"
If Range("a3").Value <= 1 Then
Range("b3") = ""
Else:
Range("b3").Value = Range("A3") & "MyText"
End If
End If
End If
End Sub

[Bob Phillips]

I been trying to incorporate this sample of "For i" above, without the "Step", to consolidate the sample code below. No sucess. Will a variation of that work? Or another way would be better? Thx for any feed back.


Do you mean



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long
For i = 1 To 4
If Range("A" & i).Value <= 1 Then
Range("B" & i) = ""
Else
Range("B" & i).Value = Range("A" & i) & "MyText"
End If
Next i
End Sub

[Desert Piranha]

Hi xld,

Exactly, Thx very much. I couldn't get it right. I almost had it, but i was missing the "& i"

Thx again

My post is SOLVED but it has nothing to do with "jester21's" stuff here.

Do you mean



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long
For i = 1 To 4
If Range("A" & i).Value <= 1 Then
Range("B" & i) = ""
Else
Range("B" & i).Value = Range("A" & i) & "MyText"
End If
Next i
End Sub