excel relative positioning needed 1

[thelos]

i would like to know how i can find the depth of a column then use that amount to set the depth of other columns in excel.
for example if my first column is (a2:a40) and i have a formula to paste in the next column that i only want to go to the same depth the reason i ask is because the informationm my macro will have to move about always has different depths some columns start empty some end empty and others are non contiguous through out any help u can give me would be much a

 

[SkipVought]

Hi,

 with range(cells(2, "A"),cells(2, "A").end(xldown))
   lRowStart = .Row
   lRowEnd = lRowStart + .Rows.Count - 1
 end with 
'the next column
 Range(cells(lRowStart, 2), cells(lRowEnd, 2)).select

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[thelos]

thanks pullemall ill give it a try

 

[thelos]

not sure how to use this code SkipVought

 

[SkipVought]

Tell me what you want to do with the code.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[thelos]

i am working with a download from sap when i import the information i have to move a few things about then insert a couple of formulas.
the problem im having is column b is empty i need my maacro to call the column "WE" then input a formula and autofill to the same depth as column a.
You see each download my macro has to deal with is always a different depth so its tellin the column b how far to autofill ive tried recording it but that only gives me absolute co ordinates.
i hope im not rambling
thanks

 

[SkipVought]

Are you using SAP queries where the worksheet has a summary block in the first few columns/rows followed by the data table?

 with range(cells(2, "A"),cells(2, "A").end(xldown))
   lRowStart = .Row
   lRowEnd = lRowStart + .Rows.Count - 1
 end with 
 with range(cells(lRowStart, "B"), cells(lRowEnd, "B"))
    .Offset(-1).Value = "WE"
    .Formula = whateverformulayouhave
 end with

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]