Autofill - till data found 1

[rampure]

Hi,

I am using a couple of macros to automate in my spreadsheet. I need to copy the formula and fill the entire column till data is found. Right now, the macro is filling, but it is going beyond the data rows and filling in for the empty rows too.

Can someone help me please.

Thanks and appreciate your response.
\
Smitha

 

[PHV]

What is your actual code and where are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rampure]

Right now I am using this code.

Sheets("GM TYLY").Select
Columns("D").Select
Selection.Insert Shift:=xlToRight
Range("D5").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC[1]=0,RC[6]=0,RC[11]=0,RC[21]=0,RC[26]=0,RC[31]=0),""X"","""")"
Range("D5").Select
Selection.AutoFill Destination:=Range("D51000")
Range("D51000").Select
Calculate


But this is filling for the empty cells too. The rows for the file that I work varies, hence I have to fill a range instead of autofill.

Thanks,
Smitha

 

[PHV]

what about this ?

With Sheets("GM TYLY")
    .Columns("D:D").Insert Shift:=xlToRight
    .Range("D5").FormulaR1C1 = _
        "=IF(OR(RC[1]=0,RC[6]=0,RC[11]=0,RC[21]=0,RC[26]=0,RC[31]=0),""X"","""")"
    .Range("D5").AutoFill Destination:=Intersect(.Range("D5:D1000"), .UsedRange)
End With
Calculate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rampure]

PH,

Thanks so much for your response.
When I tried to use it, it gave me and "Object Required" error.

I am so sorry for asking the basic questions but I am just a beginner. Should I be using just .UsedRange or something else to it?

Thanks again.
Smitha

 

[PHV]

Which line of code is highlighted when the error raises ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rampure]

.Range("D5").AutoFill Destination:=Intersect(.Range("D51000"), .UsedRange)

The macro stops at this line.

THanks
Smitha

 

[PHV]

Sorry, but the code I've posted works for me (XL2003)

 

[rampure]

It gives me a run time error 424 and stops.

When I tried to run the macro, it made me to remove . next to Range and UsedRange.

Anyways, appreciate your response.

Thanks,
Smitha

 

[PHV]

What is YOUR actual code ?

 

[rampure]

Sheets("GM TYLY").Select
Columns("D").Insert Shift:=xlToRight
Range("D5").FormulaR1C1 = _
"=IF(OR(RC[1]=0,RC[6]=0,RC[11]=0,RC[21]=0,RC[26]=0,RC[31]=0),""X"","""")"
Range("D5").AutoFill Destination:=Intersect(Range("D51000"), UsedRange)
Calculate

Thanks for your patience PH.
Smitha

 

[PHV]

Why didn't you use the (tested) code I gave you 17 May 07 8:09 ?

 

[rampure]

I did use that code. But it still would fill in the cells with empty rows.
Since I am checking for cells with empty rows, the X tend to fill in the D column.
I want the autofill to stop when the empty row begins.

Hope I am clear.

Thanks again,
Smitha

 

[PHV]

You prefer something like this ?

With Sheets("GM TYLY")
    .Columns("D:D").Insert Shift:=xlToRight
    .[D5].FormulaR1C1 = _
        "=IF(OR(RC[1]=0,RC[6]=0,RC[11]=0,RC[21]=0,RC[26]=0,RC[31]=0),""X"","""")"
    .[D5].AutoFill Destination:=.Range("D5:D" & .[A5].End(xlDown).Row)
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rampure]

Thank you so much PH. It works great.

Smitha