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]

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

I'm not a professional programmer, so my answer may be wrong, but
I don't think they fulfill the same function.

"why not use if/then/else?"
There is no reason not to use it, as it should work here.

 

[Joe4]

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

I am just commenting on this specific question, not anything else in your thread.

if/then/else can get really messy and convoluted. If you are nesting many levels in, it can be hard to read/follow/understand.

If you have a lot of options, and a situation like "If A do this, if B do this, if C do this,..., else do this", a CASE statement is MUCH easier to program and follow.
Personally, if it requires more than one "ELSE" clause, then I will lean towards using CASE.

However, if you have complex conditions that require lots of AND and OR clauses, that makes it a bit more difficult to use CASE.

 

[Keebler]

A dictionary contains a key and a value.
The key must be unique, otherwise it will throw an exception.
It prevents the creation of duplicates, but it also enables the possibility to check if the key exists. In other methods, the existence of the value must be checked by looping through the list, array, etc.
I don't know if this clears anything up?

I'm not a professional programmer, so my answer may be wrong, but
I don't think they fulfill the same function.

"why not use if/then/else?"
There is no reason not to use it, as it should work here.

looking at the MS references. they APPEAR to provide the same function. but, if they provide the same function, why would they need to commands that perform the same task?

 

[Joe4]

I'm not a professional programmer, so my answer may be wrong, but
I don't think they fulfill the same function.

They don't actually work exactly the same, but there is a lot of overlap (where you could use one or the other).

 

[Keebler]

I am just commenting on this specific question, not anything else in your thread.

if/then/else can get really messy and convoluted. If you are nesting many levels in, it can be hard to read/follow/understand.

If you have a lot of options, and a situation like "If A do this, if B do this, if C do this,..., else do this", a CASE statement is MUCH easier to program and follow.
Personally, if it requires more than one "ELSE" clause, then I will lean towards using CASE.

However, if you have complex conditions that require lots of AND and OR clauses, that makes it a bit more difficult to use CASE.

joe4,
thank you
that really helps clarify this

 

[Keebler]

so, would it be fair to say that the CASE function works best for a long list of potential outcomes with only one else clause?

 

[Joe4]

looking at the MS references. they APPEAR to provide the same function. but, if they provide the same function, why would they need to commands that perform the same task?

Why have the SUM function when you could just use "+"?

Because it is a lot easier to write:

=SUM(A1:G1)

then it is to write:

=A1+B1+C1+D1+E1+F1+G1

Some things are there to make certain tasks easier.
But while they are similar, they are not always the same!

For example, even in the simple demonstration above, if any one of the cells in A1:G1 is a text entry, the SUM calculation will work (SUM ignores text entries), but the addition one will return an error!

 

[Joe4]

so, would it be fair to say that the CASE function works best for a long list of potential outcomes with only one else clause?

For that situation, I would say Yes.

 

[Tupe77]

I'm a little curious how that should be implemented with the CASE function?

 

[Keebler]

you are correct, writing a1+b1+c1+d1 is tedious and prone to mistakes, sum(a1:d1) is what I use. to be fair, i dont guess i ever thought about writing it out long form.
but if you had to skip cells then you would have no choice but to write it out long form
a1+c1+e1+g1