offset?

[ward376]

Whenever I use the following method to write formulas/functions into
worksheets with VBA, I get an extra line (row) with the formula. (I use
the last line to workaround) Is it because of the offset? Is there a
better way to workaround or avoid?

Sub convertDate()
With ThisWorkbook.Sheets("Sheet1").Range("a1") _
..CurrentRegion.Offset(1, 0)
..Columns(2).FormulaR1C1 = _
"=VALUE(CONCATENATE(MID(RC[-1],2,2),""/"",RIGHT(RC[-1],2),""/"",LEFT(RC[-1]­,1)))"

..Columns(2).NumberFormat = "mm/dd/yy;@"
..Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

The function/formula is irrelevant; the question I have is about how to
get it in there without the "hanger". The hanger comes one row below
the UsedRange - is it due to the offset and how can it be avoided? Or
can it?



(The example is a response to -

From: SITCFanTN - view profile
Date: Sun, Jun 4 2006 9:55 pm
Email: SITCFanTN <[email protected]>
Groups: microsoft.public.excel.programming
Not yet ratedRating:
show options


Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


I have a report that I download into Excel. The report has dates for
many
months. The current date format is ymmdd (ie 60530) and I'd like to
convert
it to an easier to read format like 05/30/06. Is there code that I can
use
in an existing page setup macro to do this for all rows in the
speadsheet?
I tried using the Data\Text to Columns\and changed the text to a long
date,
but it did not do it properly, it read the 60530 as 5/30/1960! Oh is
there
code to automatically add 200 before the 60530. I added that and ran
the Data
Text to Column and it converted properly.
Thanks )

 

[mudraker]

This line CurrentRegion.Offset(1, 0) says to got to 1 row below the
Current Region.

It sounds like you need to work with the CurrentRegion only which means
that you do not need the offset function. or change the code to
CurrentRegion.Offset(0, 0)

 

[ward376]

I use the offset to leave headings intact.

 

[ward376]

So it is caused by the offset. How can I avoid this? I need to leave
the header alone.

 

[Dave Peterson]

Use a combination of offset and resize.

dim rng1 as range
dim rng2 as range
set rng1 = whateverrangeyouwant

with rng1
set rng2 = .resize(.rows.count-1).offset(1,0)
end with

one fewer row in rng2 than in rng1 (and the number of columns weren't specified,
so they didn't change) and then down one row.

 

[ward376]

Thanks!