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

✔ 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").DeleteFurther, 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").DeleteNote: 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

existingColumns 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).RowIf 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

RowIndexandColumnIndexarguments 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).RowIn 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

ColumnsIndexargument. e.g.For myCol = 1 To 6 LR = Cells(Rows.Count, myCol).End(xlUp).Row NextOnce 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 & ")" NextThe same method can be used to designate the Dynamic Ranges for your Sort routine. Use the Cells method with variables for the

RowIndexandColumnIndexarguments.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.

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

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.

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

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.

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 thisSelection.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:=xlSortNormalthe problem is the range it is hard coded i want it to become dynamic so that i can aplied it to different ranges.

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").DeleteFurther, 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").DeleteNote: 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

existingColumns 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).RowIf 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

RowIndexandColumnIndexarguments 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).RowIn 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

ColumnsIndexargument. e.g.For myCol = 1 To 6 LR = Cells(Rows.Count, myCol).End(xlUp).Row NextOnce 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 & ")" NextThe same method can be used to designate the Dynamic Ranges for your Sort routine. Use the Cells method with variables for the

RowIndexandColumnIndexarguments.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.

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.

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.

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.

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

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

originalColumns 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 whichoriginalColumns 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).DeleteThen you find the last cell in Column A with this instruction:

LR = Cells(Rows.Count, 1).End(xlUp).Rowand 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.

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.

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.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History