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)

 

[Coyotex3]

Hi, Are you able to post an example of your data(with any sensitive information removed) along with the expected results?

 

[Keebler]

Forgive me, I am not completely sure I know what you are looking for.
I am wanting to know if there is a way of joining variables
ex:
scol = "a"
srow = 3

what I want is a way to join the defined variables into
sadd = Range(scol srow) to produce Range("a3") instead i get an error compile error (expected list separator or ))
or since the srow is a constant this "variable" is the only constant though out the workbook (the starting row)
sadd = Range(scol & 3) to produce Range("a3") instead I get no compile error, but in the debug it shows the variable "sadd" is empty or has "nothing"

End ex (lol)

side note:
there are no issues combining any "row" variable. I am just having an issue combining "column" variables. I am sure it is something silly. but, i seem to be missing it.. please point me in the correct direction. I have tried using .cells(); but it seems that the .cells() only works in the range() with two or more cell() references. (which will not work for what I am needing - I need a single address such as "a3")
update:
when I used the sadd = Range(Cells(srow, scol), Cells(srow, scol)) the cell contents where produced, not the cell address (which is what I am looking for)

so, when I used sadd = Range(Cells(srow, scol), Cells(srow, scol)).Address BINGO!!! that produced the desired result.

now,
my next delema
is there a way to do a SORT that removes the blanks in the column? the way I am doing it works, but it takes a LONG time.. well over 30mins for a column with 10k+ rows

 

[Keebler]

is it possible that simply turning the screenupdate off will speed the process up?

 

[Keebler]

Can variables be redefined later in vba?
ex:
scol = "ag" (starting column)

scol = "ax" (new starting column needed)

end ex
this would allow reducing the number of variables that I have almost in half. (becuase I need to redefine several variables through out the vba code run)

 

[Keebler]

Can variables be redefined later in vba?
ex:
scol = "ag" (starting column)

scol = "ax" (new starting column needed)

end ex
this would allow reducing the number of variables that I have almost in half. (becuase I need to redefine several variables through out the vba code run)

yes

 

[RoryA]

sadd = Range(scol srow) to produce Range("a3") instead i get an error compile error (expected list separator or ))

It's:

sadd = scol & srow

assuming sadd is a string. You can then refer to Range(sadd) wherever you need it.

 

[Keebler]

it is (Dim sadd As String)
thank you

so, this brings me to yet another issue..

is there a way to list all the worksheets (in this workbook) and select all but 2 (named : "index" and "data") (maybe 3 sheets to include "FMA0101")

so, here is the why

I have a code that selects all the (needed) data on my working page ("FMA0101") It is not always this sheet that I am updating, but usually.
then I have been going to each subsequent page to update by copy and pasting into the start cell (f1) of each page (its tedious)

is there a way to select each worksheet (not mentioned above) to paste the updated data into without having to go to each page individually?
the number and names of worksheets is constantly evolving, so using a predefined list would be a no go. However, I do have on the data page a list of all the worksheets by name that is updated frequently, including the 2 or 3 sheets I do not want included in the updates...
there are currently 21 sheets (including the 2 or 3 I do not want to update (mentioned above))
is this possible?

I think that it will involve a loop (which is fine), but wont that be slow - potentially?

thank you

 

[RoryA]

Depends on your definition of slow.

I have to ask why you would need to copy and paste the same data to multiple sheets?

 

[Keebler]

Depends on your definition of slow.

I have to ask why you would need to copy and paste the same data to multiple sheets?

because the rest of the pages in the work book need to have all the same formatting, formulas, etc (Except for the 2 mentioned sheets)