Varying Range in Excel macro 2

[kabushnell]

I am creating a macro in excel that uses formulas to create new columns based on previous columns in the worksheet. The problem I am having is that the range of rows varies everytime. How do I create a macro that will only create the same number of rows that is in the worksheet everytime. This is what I have right now.

Sub DailyProcessingPart2()
'
' DailyProcessingPart2 Macro
' Macro recorded 6/10/2003 by Keith Bushnell
'

'
Sheets("transpose&quot.Select
Selection.SpecialCells(xlCellTypeLastCell).Select
Range("A58&quot.Select
Selection.Clear
Range("I57&quot.Select
ActiveCell.FormulaR1C1 = "=REPLACE(RC[-8],7,9,""/03"&quot"
Range("J57&quot.Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],2,8)"
Range("K57&quot.Select
ActiveCell.FormulaR1C1 = "=MID(RC[-8],2,6)"
Range("L57&quot.Select
ActiveCell.FormulaR1C1 = "=MID(RC[-8],2,25)"
Range("M57&quot.Select
ActiveCell.FormulaR1C1 = "=MID(RC[-8],2,10)"
Range("N57&quot.Select
ActiveCell.FormulaR1C1 = "=RC[-6]"
Range("I57:N57&quot.Select
Selection.AutoFill Destination:=Range("I1:N57&quot, Type:=xlFillDefault
Range("I1:N57&quot.Select
End Sub

The problem is that some days there can be more than 57 rows and others less. How do I set this up so that it will auto fill the proper amount.

Thanks for any help.

 

[VBAjedi]

Keith,

A couple of thoughts:

First, the macro recorder in Excel does things the slow way. In reality, you very rarely need to select a cell before doing something to it. I modified your code (see following) to reflect this.

Second, when you are working with Ranges, you can refer to them with a combination of quoted strings and variables - so long as when they all get put together it is a valid range name. Here's some code:

NumRows = 57
Range("A58").Clear
Range("I" & NumRows).FormulaR1C1 = "=REPLACE(RC[-8],7,9,""/03"")"
Range("J" & NumRows).FormulaR1C1 = "=MID(RC[-1],2,8)"
Range("K" & NumRows).FormulaR1C1 = "=MID(RC[-8],2,6)"
Range("L" & NumRows).FormulaR1C1 = "=MID(RC[-8],2,25)"
Range("M" & NumRows).FormulaR1C1 = "=MID(RC[-8],2,10)"
Range("N" & NumRows).FormulaR1C1 = "=RC[-6]"
Range("I" & NumRows & ":N" & NumRows).AutoFill Destination:=Range("I1:N" & NumRows), Type:=xlFillDefault
Range("I1:N" & NumRows).Select

All you have to do is decide how you want to set the value of NumRows each day.

Let me know if this helps! There's a number of ways to go about this, but I wanted to keep it somewhat close to your original code.

VBAjedi

 

[SkipVought]

Hi Keith,

Try this,,,

Sub DailyProcessingPart2()
    Dim LastRow As Long
'
' DailyProcessingPart2 Macro
' Macro recorded 6/10/2003 by Keith Bushnell
' Modified by SkipVought: Skip@TheOfficeExperts.com

'
    Sheets("transpose").Activate
    LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
    Cells(LastRow, 1).Clear
    Cells(LastRow, "I").FormulaR1C1 = "=REPLACE(RC[-8],7,9,""/03"")"
    Cells(LastRow, "J").FormulaR1C1 = "=MID(RC[-1],2,8)"
    Cells(LastRow, "K").FormulaR1C1 = "=MID(RC[-8],2,6)"
    Cells(LastRow, "L").FormulaR1C1 = "=MID(RC[-8],2,25)"
    Cells(LastRow, "M").FormulaR1C1 = "=MID(RC[-8],2,10)"
    Cells(LastRow, "N").FormulaR1C1 = "=RC[-6]"
    With Range(Cells(LastRow, "I"), Cells(LastRow, "N"))
        .AutoFill _
            Destination:=Range("I1:N" & LastRow), Type:=xlFillDefault
        .Select
    End With
End Sub

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[kabushnell]

I have tried both of these suggestions and both work. Thank you very much. Two questions though.

VBAjedi-Is there anyway to have the macro determine NumRows automatically?

SkipVought-The macro works great except it gives me two extra rows of formulas. Any suggestions.

Thanks for the help.

 

[SkipVought]

Hi,

Try substituting...

LastRow = ActiveSheet.Cells(1,1).CurrentRegion.Rows.Count + 1

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

...that is assuming that ALL your data is contiguous!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[kabushnell]

Thanks a bunch. That works great. One other quick question. If I want to select the range of the new data that was created and then paste those values only into a new worksheet, how would I write code to select those newly created data?

 

[SkipVought]

New compared to what? Are we talking new rows only?

If so, before you start entering "NEW" data, you need to capture the last row number.

After you are done entering "NEW" data, the range to copy would be...

Dim CopyRange as Range, LastRow as Long, LastCol as Integer
With ActiveSheet.Cells(1,1).CurrentRegion
   LastRow = .Rows.Count
   LastCol = .Columns.Count
End With
Set CopyRange = Range(Cells(LastRowBeforeNEW, 1), Cells(LastRow, LastCol))
CopyRange.Copy _
   Distination:= Sheets(2).Cells(1,1)

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com