VB 6.0 - AutoSum of variable length lists

Hello All,

I am trying to import variable length list data from SAP into excel. The format of the data is always the same, but the length of the list data changes every time. 

I can import the data, but am having difficulty summing the column as the list length varies.

I have been examining code similar to the following, but now need some way of positioning the autosum at the bottom of the list every time.......any help would be greatly appreciated.


Sub EnterAutoSum () 

 vRowTop = 5 
 vRowBottom = ActiveCell.Offset(-1, 0).Row  
 vDiff = vRowBottom - vRowTop + 1 
Selection.FormulaR1C1 = "=SUM(R[" & -vDiff & "]C:R[-1]C)" 

End Sub 


Newbie to VBA.

Chris P.

loop through the cells wit a counter and when you find an empty one, you are at the end of the list. This assumes that the values are contiguous in the spreadsheet. Since the daa is numeric you can also try the IsNumeric function on the cell contents.

This macro will work with variable length rows or columns.  It does so by the first 2 lines.  Play around with it and you'll see how it works and should be able to apply it to your situation.

Sub sort1() 
   ' 
   ' sort Macro 
   NumRecsdown = Range("A8").End(xlDown).Row 
   NumRecsacross = Range("K6").End(xlToRight).Column 

   Range("A8:" & NumRecsacross & NumRecsdown).Select 
   Selection.Sort Key1:=Range("J8"), Order1:=xlDescending, Header:=xlNo, 
   OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
   ActiveWindow.ScrollRow = 1 
End Sub

Hello Phillip,

Thanks for replying to my question. I opened a new, blank workbook. Opened VB editor and then pasted in your code.

When I tried to compile the code, it threw it out at the following line:-

   Selection.Sort Key1:=Range("J8"), Order1:=xlDescending, Header:=xlNo,
 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

complaining of the following error:-

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Syntax error
---------------------------
OK   Help   
---------------------------

I have this code inserted as a module, do I need to define anything or should it just work?

Your further help would be appreciated.

Regards,

Chris

Sure...  Here is the original link to the code and the fix that you need.

http://www.mrexcel.com/archive2/51000/59308.htm

This is the fix that worked for them.

Range("A8").Resize(NumRecsdown -8+1,NumRecsacross -columns("A").column+1) _ 
.Sort Key1:=Range("J8"), Order1:=xlDescending, Header:=xlNo, 
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

I want to read a specific field from xml file and corresponding to that field value i want to select the data stored in the xml file.