Solved dynamic range and sum

August 17, 2015 at 11:07:45
Specs: Windows 7
hello guys,

how can i write a code in such a way that i can change the range easilly from workbook to workbook and omit the last row for the entire dataset.

will this do it?

Dim IR as long
        with active workbook.worksheets("sheet1")
        IR= .Range("A" &.rows.count) end(xlup).row


See More: dynamic range and sum

Report •

✔ Best Answer
August 24, 2015 at 07:01:55
OK, lots of stuff going on here.

Let's start with the snippet of code that you included in response #1. It is very rare that you need to Select on object (e.g. a Range or a Column) in VBA in order to perform an operation on it. You can typically reference the object directly in the same line as the operation.

Each of these types of instructions...

Columns("J:L").Select
        Selection.Delete

...can be replaced with this:

Columns("J:L").Delete

Further, you can reference the entire set of Columns as one Range. For example, this instruction will delete the existing Columns J, K, L, N, O, P.

Range("J:L,N:P").Delete

Note: The major difference between that instruction and the way your code is written is that your code will delete certain columns, which will shift the remaining columns to the left. You now have to determine the "new" column letters before you can reference the additional columns that you want to delete. The single instruction that I suggested will delete the existing Columns J, K, L, N, O, P without any need for you determine any new Column letters after each of your deletion instructions.

re: How can i delete the last entry of every column and replace it with this above condicion?

This answer depends on whether or not all of your columns are the same length.
Since you are familiar with this instruction, you already know how to determine the last cell with data in Column A.

LR = Range("A" & Rows.Count).End(xlUp).Row

If all of your columns are the same length, then you use LR as the Row designation for all columns. If not, things get a bit more complicated.

Another way to reference a Range is with the Cells method. The Cells method uses RowIndex and ColumnIndex arguments to designate a cell.

For example, Cells(1, 1) references A1 as does Cells(1, "A"). Cells(3, 4) and
Cells(3, "D") both reference D3.

Therefore, these instructions are all equivalent:

LR = Range("A" & Rows.Count).End(xlUp).Row
LR = Cells(Rows.Count, "A").End(xlUp).Row
LR = Cells(Rows.Count, 1).End(xlUp).Row

In addition, you can reference a range using the Cells method as follows:

Range(Cells(1, 1), Cells(3, 4))

That instruction will reference A1:D3

If your columns are all different lengths, then you need to determine the last cell in every column separately, which you can do by looping through your columns and using a variable for the ColumnsIndex argument. e.g.

For myCol = 1 To 6
 LR = Cells(Rows.Count, myCol).End(xlUp).Row
Next

Once you determined an LR for each column, you can use that value to place your formula in the last cell of the column:

For myCol = 1 To 6
   LR = Cells(Rows.Count, myCol).End(xlUp).Row
     Cells(LR, myCol).Formula = "=SUM(" & Cells(1, myCol).Address _
                                  & ":" & Cells(LR - 1, myCol).Address & ")"
Next

The same method can be used to designate the Dynamic Ranges for your Sort routine. Use the Cells method with variables for the RowIndex and ColumnIndex arguments.

If you have any other questions, don't hesitate to ask.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
August 19, 2015 at 11:56:20
I think i am not making myself clear i think? I will try to elaborate and hope that somebody can help me with this.
so my macro looks likes this:
Windows("abc.xls").Activate
        Cells.select
        Selection.Copy
        windows("xyz.xls").Activate
        Activatesheet.Paste
        Columns("J:L").select
        Selection.delete
        Columns("K:K").select
        Selection.delete
       Columns("O:P").select
       Selection.delete
       Columns("Q:Z").select
        Selection.delete
       sheets(sheet12),select
       Columns("O:P").select
       Selection.Copy
       sheets(sheet13),select
       Range("Q").select
       Selection.Paste

From trhis point i get lost because what i want is to work with dynamic ranges.
from this point i want to delete the last entry from each column and replace it with a sum function for each column de last cel with an entry. from(" A:V").

Can somebody help me please.

message edited by debi110


Report •

#2
August 20, 2015 at 18:37:12
Thanks for clarifying your code. Unfortunately, I do not have access to Excel or VBA at the present time, so if you can be patient, I can get to your question in a day or so.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
August 20, 2015 at 21:35:36
Hi derbydad how are you doing?
i am almost there this is what i have as part of the solucion:
Dim LR as long
         LR=("A" & rows.count).end(xlup).row
         Range("A"&LR+1).Formula="=sum(A1:A"&LR&")"

This sums the total column and puts its in the last cel of this column, but i still have some question.
How can i delete the last entry of every column and replace it with this above condicion?
The last column has formatting in color and is a negative value this code gets a little messed up when aplying it for that column how can i fix this? also a part of the code has some sorting in it what is the best way to put dynamic ranges in mine sorting now i have them hard coded and i can not aply them to other data sets i am working with excel 2003.

thanks derbydad and take care

message edited by debi110


Report •

Related Solutions

#4
August 22, 2015 at 03:28:10
First, a posting tip:

It looks as if you are typing your code into your posts instead of copying it. I think it would be better if you copied your code from the VBA editor into your posts so that we see exactly what the VBA editor contains. This lessens the chance of a typographical error, which could be confusing to us.

Second, since the only technology I have access to at this time is an iPad, fully answering your questions is a bit inconvenient. I should be able to sit down in front of a workstation tomorrow where it will be easier for me to offer some code and other suggestions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
August 22, 2015 at 09:54:11
Yes the problem is that i write this from home i do not have the acces to mine code from home. so i print it out and write it down.
but the sorting part looks likes this
Selection.AutoFilter
    Range("A1:V45").Sort Key1:=Range("V1"), Order1:=xlAscending, Header:= _
     xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A1:V45").Sort Key1:=Range("V1"), Order1:=xlDescending, Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

the problem is the range it is hard coded i want it to become dynamic so that i can aplied it to different ranges.


Report •

#6
August 24, 2015 at 07:01:55
✔ Best Answer
OK, lots of stuff going on here.

Let's start with the snippet of code that you included in response #1. It is very rare that you need to Select on object (e.g. a Range or a Column) in VBA in order to perform an operation on it. You can typically reference the object directly in the same line as the operation.

Each of these types of instructions...

Columns("J:L").Select
        Selection.Delete

...can be replaced with this:

Columns("J:L").Delete

Further, you can reference the entire set of Columns as one Range. For example, this instruction will delete the existing Columns J, K, L, N, O, P.

Range("J:L,N:P").Delete

Note: The major difference between that instruction and the way your code is written is that your code will delete certain columns, which will shift the remaining columns to the left. You now have to determine the "new" column letters before you can reference the additional columns that you want to delete. The single instruction that I suggested will delete the existing Columns J, K, L, N, O, P without any need for you determine any new Column letters after each of your deletion instructions.

re: How can i delete the last entry of every column and replace it with this above condicion?

This answer depends on whether or not all of your columns are the same length.
Since you are familiar with this instruction, you already know how to determine the last cell with data in Column A.

LR = Range("A" & Rows.Count).End(xlUp).Row

If all of your columns are the same length, then you use LR as the Row designation for all columns. If not, things get a bit more complicated.

Another way to reference a Range is with the Cells method. The Cells method uses RowIndex and ColumnIndex arguments to designate a cell.

For example, Cells(1, 1) references A1 as does Cells(1, "A"). Cells(3, 4) and
Cells(3, "D") both reference D3.

Therefore, these instructions are all equivalent:

LR = Range("A" & Rows.Count).End(xlUp).Row
LR = Cells(Rows.Count, "A").End(xlUp).Row
LR = Cells(Rows.Count, 1).End(xlUp).Row

In addition, you can reference a range using the Cells method as follows:

Range(Cells(1, 1), Cells(3, 4))

That instruction will reference A1:D3

If your columns are all different lengths, then you need to determine the last cell in every column separately, which you can do by looping through your columns and using a variable for the ColumnsIndex argument. e.g.

For myCol = 1 To 6
 LR = Cells(Rows.Count, myCol).End(xlUp).Row
Next

Once you determined an LR for each column, you can use that value to place your formula in the last cell of the column:

For myCol = 1 To 6
   LR = Cells(Rows.Count, myCol).End(xlUp).Row
     Cells(LR, myCol).Formula = "=SUM(" & Cells(1, myCol).Address _
                                  & ":" & Cells(LR - 1, myCol).Address & ")"
Next

The same method can be used to designate the Dynamic Ranges for your Sort routine. Use the Cells method with variables for the RowIndex and ColumnIndex arguments.

If you have any other questions, don't hesitate to ask.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
August 25, 2015 at 10:16:54
Thank yo so much for the feedback derby it is exciting learning new things, but at the same time frustating, i am working on the code it still doesn't do what i want but i will fight with it for a while i will get back to you.
Thank you for your patient.

Report •

#8
August 25, 2015 at 11:39:26
Sub vofin()
Dim LR As Long

    Windows("XXX.xls").Activate
    Cells.Select
    Selection.Copy
    Windows("XXX201.xls").Activate
    ActiveSheet.Paste
    Columns("J:L").Select
    Application.CutCopyMode = False
    Selection.Delete
    Columns("K:K").Select
    Selection.Delete
    Columns("O:P").Select
    Selection.Delete
    Columns("Q:Z").Select
    Selection.Delete
    Range("Q1").Select
    Sheets("SHEET2").Select
    Columns("Q:V").Select
    Selection.Copy
    Sheets("SHEET1").Select
    ActiveSheet.Paste
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & LR).Delete
    LR = Range("D" & Rows.Count).End(xlUp).Row
    Range("D" & LR).Delete
     LR = Range("E" & Rows.Count).End(xlUp).Row
    Range("E" & LR).Delete
    LR = Range("F" & Rows.Count).End(xlUp).Row
    Range("F" & LR).Delete
    LR = Range("G" & Rows.Count).End(xlUp).Row
    Range("G" & LR).Delete
    LR = Range("H" & Rows.Count).End(xlUp).Row
    Range("H" & LR).Delete
     LR = Range("I" & Rows.Count).End(xlUp).Row
    Range("I" & LR).Delete
     LR = Range("J" & Rows.Count).End(xlUp).Row
    Range("J" & LR).Delete
     LR = Range("K" & Rows.Count).End(xlUp).Row
    Range("K" & LR).Delete
     LR = Range("L" & Rows.Count).End(xlUp).Row
    Range("L" & LR).Delete
     LR = Range("M" & Rows.Count).End(xlUp).Row
    Range("M" & LR).Delete
     LR = Range("N" & Rows.Count).End(xlUp).Row
    Range("N" & LR).Delete
     LR = Range("O" & Rows.Count).End(xlUp).Row
    Range("O" & LR).Delete
     LR = Range("P" & Rows.Count).End(xlUp).Row
    Range("P" & LR).Delete
    LR = Range("R" & Rows.Count).End(xlUp).Row
    Range("R" & LR).Delete
    LR = Range("S" & Rows.Count).End(xlUp).Row
    Range("S" & LR).Delete
    LR = Range("T" & Rows.Count).End(xlUp).Row
    Range("T" & LR).Delete
    LR = Range("V" & Rows.Count).End(xlUp).Row
    Range("V" & LR).Delete
    
    
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A3:V" & LR).Sort Key1:=Range("V3:V" & LR), Order1:=xlAscending, Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    
    Range("A" & LR + 1).Formula = "=sum(A1:A" & LR & ")"
    
  Range("D" & LR + 1).Formula = "=sum(D1:D" & LR & ")"
   
    Range("E" & LR + 1).Formula = "=sum(E1:E" & LR & ")"
    
    Range("F" & LR + 1).Formula = "=sum(F1:F" & LR & ")"
    
    Range("G" & LR + 1).Formula = "=sum(G1:G" & LR & ")"
    
    Range("H" & LR + 1).Formula = "=sum(H1:H" & LR & ")"
    
    Range("I" & LR + 1).Formula = "=sum(I1:I" & LR & ")"
    
    Range("J" & LR + 1).Formula = "=sum(J1:J" & LR & ")"
   
    Range("K" & LR + 1).Formula = "=sum(K1:K" & LR & ")"
   
    Range("L" & LR + 1).Formula = "=sum(L1:L" & LR & ")"
    
    Range("M" & LR + 1).Formula = "=sum(M1:M" & LR & ")"
   
    Range("N" & LR + 1).Formula = "=sum(N1:N" & LR & ")"
    
    Range("O" & LR + 1).Formula = "=sum(O1:O" & LR & ")"
    
    Range("P" & LR + 1).Formula = "=sum(P1:P" & LR & ")"
    
    Range("R" & LR + 1).Formula = "=sum(R1:R" & LR & ")"
    
    Range("S" & LR + 1).Formula = "=sum(S1:S" & LR & ")"
    
    Range("T" & LR + 1).Formula = "=sum(T1:T" & LR & ")"
    
   Range("V" & LR + 1).Formula = "=sum(V1:V" & LR & ")"
   
    
  End Sub



So this is what i got so far and it does not do what it supposed to do for the first colun it works ok but then it gets crazy becuase it sums a couple of culums but others it gives
as result no value it mixes some values and it puts its in other colums. It is really frustating.I did not change the first suggestion you gave me because it gives me an error, but i wil try it again later, the code is getting pretty large.



Report •

#9
August 25, 2015 at 13:43:17
The reason the code is getting "pretty large" is because you are performing too many individual steps that could be combined by using loops.

Without seeing your original workbook, it's hard for me to make specific suggestions, but a few things don't seem to make sense.

Why are you deleting the last cell in those columns instead of just replacing the contents with the formula?

Why aren't you putting the formula in the cells immediately after you determine the last cell with data? It appears that you are finding the last cell in a number of individual columns for the "deletion" but then you determine the last cell in Column A and use that same value for every column. Is that what you want?

If you are trying to put the formula in the last cell of each column, why not put it in the cell as soon as you determine the specific LR for each column?


      LR = Range("A" & Rows.Count).End(xlUp).Row
              Range("A" & LR).Formula = "=SUM(A1:A" & LR - 1 & ")"

      LR = Range("D" & Rows.Count).End(xlUp).Row
              Range("D" & LR).Formula = "=SUM(D1:D" & LR - 1 & ")"

etc.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
August 25, 2015 at 14:06:44
Yes the problem is that i am sorting the colums ascending, and i do not want that when i give the sort command, that it takes the last row of each colums into account, the idea is that the last rows
are deleted and then are sorted and then each column is summed, but if the method you have showed works that would be even better. I will give it a try.
It appears that you are finding the last cell in a number of individual columns for the "deletion" but then you determine the last cell in Column A and use that same value for every column. Is that what you want?

No i do not want that where do you see that can you please elaborate.

message edited by debi110


Report •

#11
August 25, 2015 at 19:28:59
re: "I did not change the first suggestion you gave me because it gives me an error,"

If you can provide a list of the original Columns that you want to delete, I might be able to offer some code that will work. Based on the fact that you are currently deleting Columns in multiple instructions, (e.g J:L then K then O:P, etc.) it is hard for me to determine which original Columns you want to delete. In other words, once the code deletes Columns J:L, the Column that is now K wasn't K before. As I mentioned in an earlier post, if you construct the instruction properly, you should be able to delete all of the columns that you don't want in one shot, from their original positions.

re: " It appears that you are finding the last cell in a number of individual columns for the "deletion" but then you determine the last cell in Column A and use that same value for every column. Is that what you want?

No i do not want that where do you see that can you please elaborate"

This long section of code finds the last cell in each of the referenced columns (one at a time) and then deletes the last cell:

    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & LR).Delete
    LR = Range("D" & Rows.Count).End(xlUp).Row
    Range("D" & LR).Delete
   ...
   ...
   ...
   ...
    LR = Range("T" & Rows.Count).End(xlUp).Row
    Range("T" & LR).Delete
    LR = Range("V" & Rows.Count).End(xlUp).Row
    Range("V" & LR).Delete

Then you find the last cell in Column A with this instruction:

LR = Cells(Rows.Count, 1).End(xlUp).Row

and then you use that value of LR for everything thing below that instruction, meaning that all formulas and your Sort instruction will use whatever Row in Column A contained the last piece of data.

       Range("A3:V" & LR).Sort Key1:=Range("V3:V" & LR), etc.
       Range("A" & LR + 1).Formula = "=sum(A1:A" & LR & ")"
       Range("D" & LR + 1).Formula = "=sum(D1:D" & LR & ")"
       etc.

VBA doesn't "remember" any previous values for a variable (e.g. LR) it only knows the last value it was set to and uses that going forward. I'm not sure that that is what you want to happen because I assume that your columns are of varying lengths.

As I mentioned earlier, without actually seeing your workbook, I can only guess at your data layout and offer code based on what I think you are trying to do. If I am way off on this next suggestion, I will have to ask for a detailed explanation of your workbook and/or that you post a copy at a site such as zippyshare so that I can see exactly what you are trying to do. My "guessing" at what you are trying to accomplish is a waste of both of our efforts.

That said, I think the following code does what your code above is attempting to do, other than the first section where you are deleting the Columns. Please note that I added comments where I deemed appropriate. This is done not only for you, but also for me so that I can remember what each section is doing. It is a good idea to use comments liberally throughout your code. You may get the code working today and then need to modify it next year. Trust me, without including comments, you won't remember what you wrote today. I know that I don't always remember why I wrote code the way I did and I use the comments to remind me.

Sub MyWay()
'Loop to clear last cell in Columns A:V, skipping B, C, Q & U
    For colNum = 1 To 22
      LR = Cells(Rows.Count, colNum).End(xlUp).Row
      
'Keep track of longest Column for sorting
        If LR > lngCol Then lngCol = LR
        
'If Column is not B, C, Q or U, Clear last cell
          If colNum <> 2 And colNum <> 3 And _
             colNum <> 17 And colNum <> 21 Then
              Cells(LR, colNum).ClearContents
          End If
    Next
    
'Sort A3:V based on longest column
    Range("A3:V" & lngCol).Sort _
        Key1:=Range("V3:V" & lngCol), _
        Order1:=xlAscending, Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        
'Place formula in last cell of Column A:V, skipping B, C, Q & U
    For colNum = 1 To 22
      LR = Cells(Rows.Count, colNum).End(xlUp).Row
      
'If Column is not B, C, Q or U, Place SUM Formula in last cell
          If colNum <> 2 And colNum <> 3 And _
             colNum <> 17 And colNum <> 21 Then
               Cells(LR + 1, colNum).Formula = _
                        "=SUM(" & _
                          Cells(1, colNum).Address & ":" & _
                          Cells(LR, colNum).Address & ")"
          End If
    Next
  End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
August 26, 2015 at 11:21:46
One last question derby en thank you for the explanation, how can i make it so that the last row of the first 19 columns where the sum formula is in adapts automatically to the last row of the longest 3 last columns. For example by leaving space blank between de last data entry en the sum formula for the shorter columns.

Report •

#13
August 26, 2015 at 17:37:39
I'm a tad confused as to why you marked this thread solved if you still have more questions, but in any case...

Sub SumLongColRow()

'Determine longest Column from T, U and V
    For colNum = 20 To 22
      LR = Cells(Rows.Count, colNum).End(xlUp).Row
       If LR > sumRow Then sumRow = LR
    Next
    
'Place SUM Formula in each Column, using sumRow as the Row
          For colNum = 1 To 22
               Cells(sumRow + 1, colNum).Formula = _
                        "=SUM(" & _
                          Cells(1, colNum).Address & ":" & _
                          Cells(sumRow, colNum).Address & ")"
          Next
  End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question