clean code

[Keebler]

background: I have learned so much from this site as well as my own research, but, as you will see, I leanred myself into a corner and would like some direction please.

I have this vba code that I use to sort a long list of titles. the code "works", but I would like a way to clean it up.
so my code defines variables then sorts based on a conglomeration of "lrow", "scol", and "srow" (see "slist")
(lrow is the last row in the column that has data) (scol is the start column) (srow is the start row) and (slist is compiled range)

1) is there a way to combine the column variable and the row variable to produce an end range? ex: (row = 3; column = 17 (Q)) to get range ("Q3") without having to change the "Q3" to match what column range I am working on? (ag3 in this case)

2) is there a way to actually sort a range removing all the blank cells? perhaps faster than the way that I am sorting the column.... row by row by row (very slow when you have over 10k rows)

3)
(in the current rendition, there are over 13k rows that amount to about 8k with the blank cells removed) removing the blank cells as they are copied would take years... ( I have modules that copy the data from various pages, then I go the index page (where the copied lists are to be sorted, etc) and paste the values, then run the sorting module, etc.

Sub SORT_ALBUM() 'SORTS ONLY THE ALBUM COLUMN(S)
Dim lrow As Long
Dim srow As String, scol As String, slist As String
Dim lrow2 As Long
Dim srow2 As String, slist2 As String
Dim CNT As Integer, C As Integer, cnum As Integer

'define variables
lrow = Range("ak1")
scol = "ag"
srow = 3
slist = "ag" & srow & ":ag" & lrow
cnum = Range(scol & 1).Column

'sort
Range(slist).Sort KEY1:=Range("ag3"), _
                     ORDER1:=xlAscending, _
                     Header:=xlNo

'remove blanks
For C = srow To lrow
    If Cells(C, cnum).Value <> "" Then
    Cells(CNT + 1, cnum + 1).Value = Cells(C, cnum).Value
    CNT = CNT + 1
    End If
Next C

'insert 2 rows
Range("ah1:ah2").Insert xlShiftDown

'define new variables
lrow2 = Range("al1") + 1
slist2 = "ah" & srow & ":ah" & lrow2

'move sorted data to correct home
Range(slist2).Copy Range("ag3")
Range(slist2).ClearContents

End Sub

so here is my vba code (this is not the whole module - only the first part that could be copied to other sections) (as I said, this works. it is just not clean, fast, or eligant)

 

[Tupe77]

can you please explain to me the addition of the dictionary?
this is a new command or reference to me, so forgive my ignorance...
i understand the loop
but not the debug statement - is that a pop-up message that tells me what sheet it is processing? (if so, how vital is it?)
the insert code lines are self explanatory

thank you, ill give this a whirl... it probably will not be today (Thursday) but I should get back to this Friday... thank you!

but not the debug statement - is that a pop-up message that tells me what sheet it is processing? (if so, how vital is it?)

Debug.Print "It's sheet " & UCase(wsTMP.Name) & " turn in loop"

print what sheet it is processing to Immediate window in VBA editor
It's just for tracking how the code works. There is no need to keep it if the code works correctly.

 

[Tupe77]

That's the whole point. The idea is not to process any of those three sheets, but to process any others.

My bad, I already revealed that I lied in the previous message.
I no longer know at what point my logic went haywire and I was suddenly thinking about it the wrong way.
Sorry!

 

[Keebler]

im getting an error
"Object doesn't support this property or method"
referencing
Case "INDEX", "DATA", aws

Sub update_ws()
Dim aws As Worksheet, wsTMP As Worksheet
Dim crg As Range
Set aws = activesheet
Set crg = Range("f:gg")

For Each wsTMP In Worksheets
   Select Case UCase$(wsTMP.Name)
      Case "INDEX", "DATA", aws
      ' do nothing
      Case Else
      crg.Copy wsTMP.Range("f1")
   End Select
Next wsTMP

End Sub

 

[RoryA]

You should be using aws.name

 

[Keebler]

THANK YOU
things that make you say "DUH"!
what an oversight on my part
THANK YOU RoryA worked perfectly!

 

[Keebler]

so here is the WORKING vba code

Sub update_ws()
Dim aws As Worksheet, wsTMP As Worksheet
Dim crg As Range
Set aws = activesheet
Set crg = activesheet.Range("f:gg")

For Each wsTMP In Worksheets
   Select Case UCase$(wsTMP.Name)
      Case "INDEX", "DATA", aws.Name
          ' do nothing
      Case Else
      crg.Copy wsTMP.Range("f1")
   End Select
Next wsTMP

End Sub

 

[Keebler]

i tried this on several other ws
same result
works great! thank you!