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).Entire Row.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
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 )