Range Offset Help

[AsgiE]

Hi,
I need guidance on how I can reduce the range selected in row 4 by 1 row.
The selection. Offset syntax moves the entire selection one row up and overwrites one of the rows above the range.


I just want the range to be reduced by 1 row.
Any help will be greatly appreciated
Thanks

Range("D9").Select
Selection.End(xlDown).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(-1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False

 

[sheetspread]

Does:

Range(Selection, Selection.End(xlDown)).resize(-1,0).Select

work?

 

[AsgiE]

No I get a "runtime error 1004" with the usual "application-defined or object defined error"

 

[Firefly2012]

You rarely ever need to Select anything to work with it in VBA. That being said, try inserting this after your 4th line:

Selection.Resize(Selection.Rows.Count-1).Select

 

[sheetspread]

So what are we trying to do, copy the range beginning in Cell D9 to the second last row of your data then pasting it 1 row after the last row?

I can write something much simpler than what you have above, without all those selects and maybe only in 1-2 lines

 

[AsgiE]

that worked as expected. Appreciate the help.
This is a quick script to copy down "names" into blank cells below with active records. The names only appear on the first row.
What would be a quicker method. I have thought about using the fill down syntax but not applied it yet.
Thanks

 

[AsgiE]

Yes that is essentially what I am trying to do. Any help with simplification would be appreciated.
Thanks

 

[sheetspread]

Here's an example (might have to be changed if your data structure isn't what I've guessed it to be):

Excel 2003
	A	B	C	D
9				Name1
10	Record1
11	Record2
12	Record3
13	Record4
14	Record5
15	Record6
16	Record7
17	Record8
18	Record9
19	Record10
20	Record11
21	Record12
22	Record13
23	Record14
24	Record15
25	Record16
26	Record17
27	Record18
28	Record19
29	Record20
30	Record21
31	Record22
Sheet3

Sub testcopydown()
Range("D9").Copy Destination:=Range("D10:D" & Cells(Rows.Count, 1).End(xlUp).Row - 1)
End Sub

Excel 2003
	A	B	C	D
9				Name1
10	Record1			Name1
11	Record2			Name1
12	Record3			Name1
13	Record4			Name1
14	Record5			Name1
15	Record6			Name1
16	Record7			Name1
17	Record8			Name1
18	Record9			Name1
19	Record10			Name1
20	Record11			Name1
21	Record12			Name1
22	Record13			Name1
23	Record14			Name1
24	Record15			Name1
25	Record16			Name1
26	Record17			Name1
27	Record18			Name1
28	Record19			Name1
29	Record20			Name1
30	Record21			Name1
31	Record22
Sheet3

 

[AsgiE]

[TABLE="class: grid, width: 150, align: left"]
<TBODY>[TR]
[TD]Record1[/TD]
[TD]Name1[/TD]
[/TR]
[TR]
[TD]Record2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record6[/TD]
[TD]Name2[/TD]
[/TR]
[TR]
[TD]Record7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record11[/TD]
[TD]Name3[/TD]
[/TR]
[TR]
[TD]Record12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record16[/TD]
[TD]Name4[/TD]
[/TR]
[TR]
[TD]Record17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record20[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
































In the above I needed to fill in Record2 to Record5 with Name1 and Record7 to Record10 with Name2 and so on. Hope this clarifies the requirement and again many thanks for the help from this forum.
Regards
Asgi

 

[sheetspread]

select the name column or range, hit f5-special-blanks, then type =d9 (or wherever the first Name1 is) and hit ctrl-enter


(if this won't work for you because it's not in VBA tell me and I'll give you some code lines)