Vba with offset copy and past

[bboysen]

Hello all.

I am having a problem with my code.
Here is what i am trying to do.

Lets say i have data in rows R1C1, R3C1 AND R5C1. I have hidden rows 2,4. Now I want to copy the info from R1C1 to R3C2. My problem happens with the hidden rows. It will copy with the offset of (0, -1) from the selected cell of R3C2, but will paste it in R2C2 when I use the offset of (1, 0).

I have looked all over the internet and know I am missing one little thing but can't seem to pinpoint the problem.

I have add my simple code to this post please let me know where I have gone wrong!

Thanks

Sub OFFSETCOPY()'
'
'SELECT AND COPY ONE C UP AND ONE R LEFT OF CURRENT SELECTION
ActiveCell.Offset(0, -1).Select
    Selection.Copy
    Do
ActiveCell.Offset(1, 0).Select
     Loop While ActiveCell.EntireRow.Hidden = True
'SELECT AND PAST ONE C DOWN AND ONE R RIGHT OF CURRENT SELECTION
    ActiveSheet.PASTE
End Sub

 

[JLGWhiz]

Your example would probably give you an error message since you are trying to offset from column A to the left. But I think I understand what you are saying. Excel still sees the rows and columns although the user may have hidden them.

With VBA you need to tell Excel to ignore the hidden rows or columns. Try this modified version. It assumes that your worksheet is sheet1. If not, make the appropriate change to the With statement.

Code:
Sub OFFSETCOPY2()'
'SELECT AND COPY ONE C UP AND ONE R LEFT OF CURRENT SELECTION
With Sheets(1).SpecialCells(xlCellTypeVisible)
ActiveCell.Offset(0, -1).Select
Selection.Copy
Do
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.EntireRow.Hidden = True
'SELECT AND PAST ONE C DOWN AND ONE R RIGHT OF CURRENT SELECTION
ActiveSheet.Paste
End With
End Sub
Code:

 

[bboysen]

Thanks that worked had to make a few tweaks to it but got it to go!

Do you think you could help me with an if statement for it to work only on rows that contain the word weld?

What I have is this:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M
Point ID	Northing	Easting	Elevation	Feature Code	Latitude (Local)	Longitude (Local)	Attributes 1	Attributes 2	Attributes 3	Attributes 4	Attributes 5	Attributes 6
LE	1168-1	F40520
WELD	F2ML-768	1016-9	1168-1	R42324
WELD	F2ML-769	1036-9	1016-9	F40320
WELD	F2ML-770	1018-9	1036-9	F40319
WELD	F2ML-771	1018-9B	1018-9	F40319
WELD	F2ML-772	1013-8	1018-9B	R42247
WELD	F2ML-773	1013-8B	1013-8	R42247
WELD	F2ML-774	1013-8D	1013-8B	R42247
WELD	F2ML-775	1015-5	1013-8D	R42324
WELD	F2ML-776	1082-4	1015-5	F40521
WELD	F2ML-777	1095-8	1082-4	F40522
WELD	A2C2F2ML-778NW	1095-8B	1095-8	F40522
WELD	F2ML-779	1000-4	1095-8B	R42245
WELD	F2ML-780	993-6	1000-4	R42243
WELD	F2ML-781	1021-2	993-6	R42248
WELD	F2ML-782	369-5	1021-2	R43792
WELD	F2ML-783	375-3	369-5	R43792
WELD	A2C2F2ML-784NW	333-6	375-3	F41756
WELD	F2ML-785	372-5	333-6	R43792
WELD	F2ML-786	372-2	372-5	R43792
WELD	F2ML-787	363-3	372-2	R43792
WELD	F2ML-788	362-4	363-3	R43792
WELD	F2ML-789	328-8	362-4	F41756
WELD	F2ML-790	346-6	328-8	F41757
WELD	C2F2ML-791R	374-4	346-6	R43792
WELD	F2ML-792	361-5	374-4	R43792
LE	361-5	R43792

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]206146[/TD]
[TD="align: right"]16716330.99[/TD]
[TD="align: right"]1830753.758[/TD]
[TD="align: right"]3313.547[/TD]

[TD="align: right"]46.00745[/TD]
[TD="align: right"]-104.25067[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]206149[/TD]
[TD="align: right"]16716260.91[/TD]
[TD="align: right"]1830751.853[/TD]
[TD="align: right"]3316.922[/TD]

[TD="align: right"]46.00726[/TD]
[TD="align: right"]-104.25068[/TD]

[TD="align: right"]28.3[/TD]

[TD="align: right"]1016[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]206152[/TD]
[TD="align: right"]16716232.59[/TD]
[TD="align: right"]1830751.318[/TD]
[TD="align: right"]3318.787[/TD]

[TD="align: right"]46.00718[/TD]
[TD="align: right"]-104.25068[/TD]

[TD="align: right"]45.9[/TD]

[TD="align: right"]1036[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]206158[/TD]
[TD="align: right"]16716187.53[/TD]
[TD="align: right"]1830750.718[/TD]
[TD="align: right"]3323.865[/TD]

[TD="align: right"]46.00705[/TD]
[TD="align: right"]-104.25068[/TD]

[TD="align: right"]21.2[/TD]

[TD="align: right"]1018[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]206159[/TD]
[TD="align: right"]16716166.38[/TD]
[TD="align: right"]1830750.383[/TD]
[TD="align: right"]3325.947[/TD]

[TD="align: right"]46.007[/TD]
[TD="align: right"]-104.25068[/TD]

[TD="align: right"]20.9[/TD]

[TD="align: right"]1018[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]206163[/TD]
[TD="align: right"]16716145.48[/TD]
[TD="align: right"]1830749.979[/TD]
[TD="align: right"]3327.325[/TD]

[TD="align: right"]46.00694[/TD]
[TD="align: right"]-104.25069[/TD]

[TD="align: right"]18.3[/TD]

[TD="align: right"]1013[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]206164[/TD]
[TD="align: right"]16716127.34[/TD]
[TD="align: right"]1830749.669[/TD]
[TD="align: right"]3328.03[/TD]

[TD="align: right"]46.00689[/TD]
[TD="align: right"]-104.25069[/TD]

[TD="align: right"]18[/TD]

[TD="align: right"]1013[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]206165[/TD]
[TD="align: right"]16716109.41[/TD]
[TD="align: right"]1830749.132[/TD]
[TD="align: right"]3328.651[/TD]

[TD="align: right"]46.00684[/TD]
[TD="align: right"]-104.25069[/TD]

[TD="align: right"]9[/TD]

[TD="align: right"]1013[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]206167[/TD]
[TD="align: right"]16716100.49[/TD]
[TD="align: right"]1830748.986[/TD]
[TD="align: right"]3328.894[/TD]

[TD="align: right"]46.00682[/TD]
[TD="align: right"]-104.25069[/TD]

[TD="align: right"]49[/TD]

[TD="align: right"]1015[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]206168[/TD]
[TD="align: right"]16716051.53[/TD]
[TD="align: right"]1830747.844[/TD]
[TD="align: right"]3329.948[/TD]

[TD="align: right"]46.00668[/TD]
[TD="align: right"]-104.2507[/TD]

[TD="align: right"]52.8[/TD]

[TD="align: right"]1082[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]206169[/TD]
[TD="align: right"]16715998.72[/TD]
[TD="align: right"]1830746.932[/TD]
[TD="align: right"]3331.266[/TD]

[TD="align: right"]46.00654[/TD]
[TD="align: right"]-104.2507[/TD]

[TD="align: right"]21.2[/TD]

[TD="align: right"]1095[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]206171[/TD]
[TD="align: right"]16715977.65[/TD]
[TD="align: right"]1830746.69[/TD]
[TD="align: right"]3331.822[/TD]

[TD="align: right"]46.00648[/TD]
[TD="align: right"]-104.25071[/TD]

[TD="align: right"]37[/TD]

[TD="align: right"]1095[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]206172[/TD]
[TD="align: right"]16715940.71[/TD]
[TD="align: right"]1830745.968[/TD]
[TD="align: right"]3332.923[/TD]

[TD="align: right"]46.00638[/TD]
[TD="align: right"]-104.25071[/TD]

[TD="align: right"]75.3[/TD]

[TD="align: right"]1000[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]206173[/TD]
[TD="align: right"]16715865.49[/TD]
[TD="align: right"]1830744.74[/TD]
[TD="align: right"]3336.127[/TD]

[TD="align: right"]46.00617[/TD]
[TD="align: right"]-104.25072[/TD]

[TD="align: right"]75.7[/TD]

[TD="align: right"]993[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]206175[/TD]
[TD="align: right"]16715791.09[/TD]
[TD="align: right"]1830743.608[/TD]
[TD="align: right"]3340.857[/TD]

[TD="align: right"]46.00597[/TD]
[TD="align: right"]-104.25073[/TD]

[TD="align: right"]78.2[/TD]

[TD="align: right"]1021[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]206180[/TD]
[TD="align: right"]16715713.4[/TD]
[TD="align: right"]1830743.015[/TD]
[TD="align: right"]3347.726[/TD]

[TD="align: right"]46.00575[/TD]
[TD="align: right"]-104.25073[/TD]

[TD="align: right"]60.5[/TD]

[TD="align: right"]369[/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]206181[/TD]
[TD="align: right"]16715644.17[/TD]
[TD="align: right"]1830741.363[/TD]
[TD="align: right"]3353.35[/TD]

[TD="align: right"]46.00556[/TD]
[TD="align: right"]-104.25074[/TD]

[TD="align: right"]76.2[/TD]

[TD="align: right"]375[/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]206182[/TD]
[TD="align: right"]16715568.2[/TD]
[TD="align: right"]1830739.822[/TD]
[TD="align: right"]3357.615[/TD]

[TD="align: right"]46.00536[/TD]
[TD="align: right"]-104.25075[/TD]

[TD="align: right"]71.6[/TD]

[TD="align: right"]333[/TD]

[TD="align: center"]40[/TD]
[TD="align: right"]206184[/TD]
[TD="align: right"]16715496.7[/TD]
[TD="align: right"]1830738.641[/TD]
[TD="align: right"]3360.608[/TD]

[TD="align: right"]46.00516[/TD]
[TD="align: right"]-104.25076[/TD]

[TD="align: right"]70.6[/TD]

[TD="align: right"]372[/TD]

[TD="align: center"]41[/TD]
[TD="align: right"]206185[/TD]
[TD="align: right"]16715426.12[/TD]
[TD="align: right"]1830737.457[/TD]
[TD="align: right"]3362.825[/TD]

[TD="align: right"]46.00497[/TD]
[TD="align: right"]-104.25076[/TD]

[TD="align: right"]66.5[/TD]

[TD="align: right"]372[/TD]

[TD="align: center"]44[/TD]
[TD="align: right"]206188[/TD]
[TD="align: right"]16715359.7[/TD]
[TD="align: right"]1830736.156[/TD]
[TD="align: right"]3365.936[/TD]

[TD="align: right"]46.00478[/TD]
[TD="align: right"]-104.25077[/TD]

[TD="align: right"]75[/TD]

[TD="align: right"]363[/TD]

[TD="align: center"]45[/TD]
[TD="align: right"]206189[/TD]
[TD="align: right"]16715284.99[/TD]
[TD="align: right"]1830734.486[/TD]
[TD="align: right"]3369.276[/TD]

[TD="align: right"]46.00458[/TD]
[TD="align: right"]-104.25078[/TD]

[TD="align: right"]62.8[/TD]

[TD="align: right"]362[/TD]

[TD="align: center"]48[/TD]
[TD="align: right"]206192[/TD]
[TD="align: right"]16715223.99[/TD]
[TD="align: right"]1830733.556[/TD]
[TD="align: right"]3370.262[/TD]

[TD="align: right"]46.00441[/TD]
[TD="align: right"]-104.25079[/TD]

[TD="align: right"]75.4[/TD]

[TD="align: right"]328[/TD]

[TD="align: center"]49[/TD]
[TD="align: right"]206193[/TD]
[TD="align: right"]16715148.53[/TD]
[TD="align: right"]1830732.895[/TD]
[TD="align: right"]3370.012[/TD]

[TD="align: right"]46.0042[/TD]
[TD="align: right"]-104.25079[/TD]

[TD="align: right"]77.1[/TD]

[TD="align: right"]346[/TD]

[TD="align: center"]51[/TD]
[TD="align: right"]206194[/TD]
[TD="align: right"]16715071.62[/TD]
[TD="align: right"]1830733.635[/TD]
[TD="align: right"]3369.029[/TD]

[TD="align: right"]46.00399[/TD]
[TD="align: right"]-104.25079[/TD]

[TD="align: right"]76.5[/TD]

[TD="align: right"]374[/TD]

[TD="align: center"]54[/TD]
[TD="align: right"]206198[/TD]
[TD="align: right"]16714995.34[/TD]
[TD="align: right"]1830734.716[/TD]
[TD="align: right"]3369.695[/TD]

[TD="align: right"]46.00378[/TD]
[TD="align: right"]-104.25079[/TD]

[TD="align: right"]76.9[/TD]

[TD="align: right"]361[/TD]

[TD="align: center"]56[/TD]
[TD="align: right"]206199[/TD]
[TD="align: right"]16714918.68[/TD]
[TD="align: right"]1830735.778[/TD]
[TD="align: right"]3372.646[/TD]

[TD="align: right"]46.00357[/TD]
[TD="align: right"]-104.25079[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

​

Now remember I have hidden Info between the welds.

Also here is my updated Code

Sub WELD()

finalrow = ActiveSheet.UsedRange.Rows.Count


Count = 1
For r = 2 To finalrow
    With ActiveCell.SpecialCells(xlCellTypeVisible)
    ActiveCell.Offset(0, 3).Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-4],SEARCH(""-"",RC[-4])-1)"
    ActiveCell.Offset(0, -4).Select
    Selection.Copy
    Do
    ActiveCell.Offset(1, 0).Select
    Loop While ActiveCell.EntireRow.Hidden = True
    ActiveCell.Offset(0, 1).Select
    ActiveSheet.PASTE
    End With
    
Next r


End Sub

And how would you get it to stop at row 56 and not go any further?

Thanks for your help