VBA gurus,
By being so active here, I am learning some VBA coding. Mostly by copying and modifying code to suit my needs.
I needed to figure out a way to:
1. Look at five columns [each is filled to different size by another macro],
and if row 1 in the respective column has text in it,
2. Find last used row in that column and
3. Sort each column alphabetically from Row 4 to end of that column.
In the attached example I have a macro "sort".
It works, but as you can see, I am using a lot of workarounds.
In hopes of learning proper syntax; to loop though columns B-F and selecting appropriate range and then sorting the range; I am asking someone to look at the code and streamline it so its more efficient.
Here's the code:
Sub sort()
Dim lastrow As Long
Dim col As Integer
Dim begin, last As String
Dim Len1 As Integer
Dim col1 As String
Dim myrange As String
For col = 2 To 6
If col = 2 Then
col1 = "B"
End If
If col = 3 Then
col1 = "C"
End If
If col = 4 Then
col1 = "D"
End If
If col = 5 Then
col1 = "E"
End If
If col = 6 Then
col1 = "F"
End If
Len1 = Len(ActiveSheet.Cells(1, col))
If Len1 > 1 Then
lastrow = ActiveSheet.Cells(Rows.Count, col).End(xlUp).Row
begin = col1 & "4"
last = col1 & lastrow
myrange = begin & ":" & last
ActiveSheet.Cells("4", col).Select
Application.CutCopyMode = False
Range(myrange).Select
Selection.sort Key1:=Range(begin), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
Next col
End Sub
Thank you for you help.
modytrane.
Bookmarks