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)

 

[Keebler]

Joe4,
lol
thank you

 

[Keebler]

Tupe77
thank you

 

[RoryA]

As far as I understand, CASE doesn't work that way?
The comma is read as OR, so whenever the loop meets the INDEX sheet, the Case "INDEX", "DATA", "SOME OTHER NAME" is executed
It doesn't get to CASE Else - part after that

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

 

[Keebler]

RoryA
thank you

 

[Keebler]

can the "some other name" be a variable tied to the current sheet that when the vba code is ran, it just picks the current ws?
like
dim aws as activesheet

 

[Tupe77]

' ***** CASE
For Each wsTMP In Worksheets
   Select Case wsTMP.Name
      Case "Index", "DATA", wsCurrent.Name
        ' do nothing
      Case Else
        Debug.Print "It's sheet " & UCase(wsTMP.Name) & " turn in loop"
   End Select
Next

 

[RoryA]

so, i have to ask
if select/case is the same as if/then/else
why not use if/then/else?

In situations like this, Select Case can be neater. Compare:

Select Case ws.Name
   Case "Sheet1", "Sheet2", "Sheet3"
    ' do something

versus:

codeIf ws.Name = "Sheet1" Or ws.Name ="Sheet2" Or ws.Name = "Sheet3" Then
    ' do something

 

[Keebler]

Select Case ws.Name
   Case "Sheet1", "Sheet2", "Sheet3"
    ' do something

would i then need to list EVERY sheet that is to be updated? or would your previous example be better?

For Each wsTMP In Worksheets
   Select Case UCase$(wsTMP.Name)
      Case "INDEX", "DATA", "SOME OTHER NAME"
      ' do nothing
      Case Else
      ' run your code here
   End Select

but i would still like to have the "some other sheet" be a defined variable
dim aws as activesheet?
Case "INDEX", "DATA", aws

would that work?

 

[RoryA]

I was just giving an example. You'd list whichever sheets have the fewest number and then either do the code in the Case or the Case Else accordingly. If you have the sheet names listed somewhere, you could also use that and then use something like Application.Match or Countif to see if each sheet name appears in the list. That way would be easier to maintain.

Yes, you can use a variable in the Case part.

 

[Keebler]

I was just giving an example. You'd list whichever sheets have the fewest number and then either do the code in the Case or the Case Else accordingly. If you have the sheet names listed somewhere, you could also use that and then use something like Application.Match or Countif to see if each sheet name appears in the list. That way would be easier to maintain.

Yes, you can use a variable in the Case part.

thank you

ill post what i come up with probably tomorrow.. i have a bunch of traveling to do today and will be back at the desk tomorrow
thank you!