I have been using VBA with Excel basically to solve one problem at a time over about 5 years. I use it so little that I forget how to construct the code without resorting to trial and error and looking for solutions by others. Recently, I was given a ginormous assignment to process a huge database, some of which has to pass through Excel for processing. Lucky me, I get to write the code to do the Excel parts.
What I get is list of 3000-5000 rows of data that is delimited. It parses out into 13 columns and then I have to line up which elements belong in each column and so some cells are empty. I have this part figured out an done.
Next I have to take each column and count the number of common elements. The number of elements can vary from 1 to very many. I have located 2 ways that work. One is to sort the column and then count and delete each element. That works but my column is gone, but I can work around it by by moving a copy of each column to a new sheet for counting. The count is what is needed anyway.
The second way is a pivot table which also works great, but I have never used a pivot table and have no idea what purpose it serves.
What I would really like to do is count each column in place and put the results somewhere on the page or add anew sheet with the summarized data. The columns will have "holes" or blank cells, so the "counter" has to ignore blank cells or count them as blank cells. Also, before anything begins, I get the top cell of the list and the bottom cell of the list for looping for the total number of items. That value plus the number of columns is a constant for each list I receive. The elements in the list are always variable.
What I get is list of 3000-5000 rows of data that is delimited. It parses out into 13 columns and then I have to line up which elements belong in each column and so some cells are empty. I have this part figured out an done.
Next I have to take each column and count the number of common elements. The number of elements can vary from 1 to very many. I have located 2 ways that work. One is to sort the column and then count and delete each element. That works but my column is gone, but I can work around it by by moving a copy of each column to a new sheet for counting. The count is what is needed anyway.
The second way is a pivot table which also works great, but I have never used a pivot table and have no idea what purpose it serves.
What I would really like to do is count each column in place and put the results somewhere on the page or add anew sheet with the summarized data. The columns will have "holes" or blank cells, so the "counter" has to ignore blank cells or count them as blank cells. Also, before anything begins, I get the top cell of the list and the bottom cell of the list for looping for the total number of items. That value plus the number of columns is a constant for each list I receive. The elements in the list are always variable.
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Did you hear what happened when the OO programmer lost his library?...![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
