Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with syntax with fill down
Excel VBA
I am trying to 1) copy D25 and copy down where Column E data ends 2) Then Copy start in D25 through Column J where data ends And put into a new worksheet starting A1. D E F G H I J 25 2805 11111 11111 1111 1111 111 111 26 11111 11111 1111 1111 111 111 27 11111 11111 1111 1111 111 111 I am having trouble with syntax of the following piece of code. Now need to figure out Test Data right now is D25:J37 with D25 having on "2805" and D26 on down blank but when I run the code for some reason the "2805" in D25 is copying down to D61 and not stop in D37 Where Column E ends in E37. Can someone tell me why? Any help would greatly be appreciated! Private Sub cmdTransferToSRP_Click() 'Copy Data and transfer to New Workbook Dim rngToCopy As Range Dim rngToPaste As Range Dim r r = Range("E65536").End(xlUp).Row Range("D25").Resize(r, 1).FillDown Set rngToCopy = Range("D25:J" & Cells(Rows.Count, "D").End(xlUp).Row -- ca1358 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with syntax with fill down
The row argument for Resize is the total number of rows in the resized range,
not the last row. Since you are not starting in row 1, you need to subtract 25 rows (and add 1 back, so you include both the starting and ending rows). Also, your statement Set rngToCopy = Range("D25:J" & Cells(Rows.Count, "D").End(xlUp).Row needs a closing right parenthesis. But it's doing all that work just to tell you row r, which you already know. I would rewrite your code fragment as follows: Private Sub cmdTransferToSRP_Click() 'Copy Data and transfer to New Workbook Dim rngToCopy As Range Dim rngToPaste As Range Dim r As Long r& = Cells(Rows.Count, 5).End(xlUp).Row Range("D25").Resize(r& - 25 + 1).FillDown Set rngToCopy = Range("D25:J" & r&) Hope this helps, Hutch "ca1358" wrote: Excel VBA I am trying to 1) copy D25 and copy down where Column E data ends 2) Then Copy start in D25 through Column J where data ends And put into a new worksheet starting A1. D E F G H I J 25 2805 11111 11111 1111 1111 111 111 26 11111 11111 1111 1111 111 111 27 11111 11111 1111 1111 111 111 I am having trouble with syntax of the following piece of code. Now need to figure out Test Data right now is D25:J37 with D25 having on "2805" and D26 on down blank but when I run the code for some reason the "2805" in D25 is copying down to D61 and not stop in D37 Where Column E ends in E37. Can someone tell me why? Any help would greatly be appreciated! Private Sub cmdTransferToSRP_Click() 'Copy Data and transfer to New Workbook Dim rngToCopy As Range Dim rngToPaste As Range Dim r r = Range("E65536").End(xlUp).Row Range("D25").Resize(r, 1).FillDown Set rngToCopy = Range("D25:J" & Cells(Rows.Count, "D").End(xlUp).Row -- ca1358 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transparency slider and Fill trouble in Autoshape | Excel Discussion (Misc queries) | |||
formula, with constant cell, trouble with fill down | Excel Discussion (Misc queries) | |||
Fill in form to type Item descrictions and costs and fill in funct | Excel Worksheet Functions | |||
trouble with viewing fill color and font color | Excel Worksheet Functions | |||
Trouble with Syntax - IF formula | New Users to Excel |