MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dynamic Colums and Summing


Posted by Brian S. on December 20, 2001 1:09 PM

Below is some code that I use to loop through columns deleting those that are empty. Now what I would like to do is that on the end column, select that column and Sum by Row(using sum(k2:(whatever last column is)2.)

The problem is that this will be dynamic and I don't know how to tell excel to use this column in the code.

Thanks,
Brian


Dim var1, var2, var3 As String
Dim count As Integer
var1 = "K"
Do While var1 <= "Z"

var2 = "4"
var3 = var1 & var2

Do While Sheets("qryOut").Range("" & var3 & "").Value = ""
If count >= 100 Then
Exit Do
Else

Columns(var1 & ":" & var1).Select
Selection.Cut
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
count = count + 1
End If
Loop

Z = Asc(var1) 'To modify for Z to be a add (64=A, 96=a)
If Z >= 123 Then
DoCmd.Exit
Else

var1 = Chr(Z + 1)
End If

Loop

*Should be on empty column ready for sums*


Posted by Joe C on December 20, 2001 2:17 PM

brecamt = _
Application.WorksheetFunction.CountA(Range("a1:cv1"))

Will count how many columns are between 1 and 100. It seems none are blank so it will le you know which is the last column.

Loop *Should be on empty column ready for sums*


Posted by Brian S. on December 21, 2001 8:33 AM

Seems to work but how do I reference it? I only get a count number not something like W4 or AA1
I need this to do additional manipulation.

Thanks brecamt = _ Application.WorksheetFunction.CountA(Range("a1:cv1")) Will count how many columns are between 1 and 100. It seems none are blank so it will le you know which is the last column.