none
How to make VBA CODE short RRS feed

  • Question

  • Dear All 

    I have following code, I want to make it short Could you please help me to make it short.

    Sub Copy()
    Worksheets("sheet1").Range("H9,H10,H11,H12,H13,H14,H15,H16,H17,H18").Copy Destination:=Worksheets("Sheet1").Range("G25")
    Worksheets("sheet1").Range("H25,H26,H27,H28,H29,H30,H31,H32,H33,H34").Copy Destination:=Worksheets("Sheet1").Range("G42")
    Worksheets("sheet1").Range("H42,H43,H44,H45,H46,H47,H48,H49,H50,H51").Copy Destination:=Worksheets("Sheet1").Range("G59")
    Worksheets("sheet1").Range("H59,H60,H61,H62,H63,H64,H65,H66,H67,H68").Copy Destination:=Worksheets("Sheet1").Range("G76")
    Worksheets("sheet1").Range("H76,H77,H78,H79,H80,H81,H82,H83,H84,H85").Copy Destination:=Worksheets("Sheet1").Range("G93")
    Worksheets("sheet1").Range("H93,H94,H95,H96,H97,H98,H99,H100,H101,H102").Copy Destination:=Worksheets("Sheet1").Range("G110")
    Worksheets("sheet1").Range("H110,H111,H112,H113,H114,H115,H116,H117,H118,H119").Copy Destination:=Worksheets("Sheet1").Range("G127")
    Worksheets("sheet1").Range("H127,H128,H129,H130,H131,H132,H133,H134,H135,H136").Copy Destination:=Worksheets("Sheet1").Range("G144")
    Worksheets("sheet1").Range("H144,H145,H146,H147,H148,H149,H150,H151,H152,H153").Copy Destination:=Worksheets("Sheet1").Range("G161")
    Worksheets("sheet1").Range("H161,H162,H163,H164,H165,H166,H167,H168,H169,H170").Copy Destination:=Worksheets("Sheet1").Range("G178")
    Worksheets("sheet1").Range("H178,H179,H180,H181,H182,H183,H184,H185,H186,H187").Copy Destination:=Worksheets("Sheet1").Range("G195")
    Worksheets("sheet1").Range("H195,H196,H197,H198,H199,H200,H201,H202,H203,H204").Copy Destination:=Worksheets("Sheet1").Range("G212")
    Worksheets("sheet1").Range("H212,H213,H214,H215,H216,H217,H218,H219,H220,H221").Copy Destination:=Worksheets("Sheet1").Range("G229")
    Worksheets("sheet1").Range("H229,H230,H231,H232,H233,H234,H235,H236,H237,H238").Copy Destination:=Worksheets("Sheet1").Range("G246")
    Worksheets("sheet1").Range("H246,H247,H248,H249,H250,H251,H252,H253,H254,H255").Copy Destination:=Worksheets("Sheet1").Range("G263")
    Worksheets("sheet1").Range("H263,H264,H265,H266,H267,H268,H269,H270,H271,H272").Copy Destination:=Worksheets("Sheet1").Range("G281")
    Worksheets("sheet1").Range("H281,H282,H283,H284,H285,H286,H287,H288,H289,H290").Copy Destination:=Worksheets("Sheet1").Range("G298")
    Worksheets("sheet1").Range("H298,H299,H300,H301,H302,H303,H304,H305,H306,H307").Copy Destination:=Worksheets("Sheet1").Range("G315")
    Worksheets("sheet1").Range("H315,H316,H317,H318,H319,H320,H321,H322,H323,H324").Copy Destination:=Worksheets("Sheet1").Range("G332")
    Worksheets("sheet1").Range("H332,H333,H334,H335,H336,H337,H338,H339,H340,H341").Copy Destination:=Worksheets("Sheet1").Range("G349")
    Worksheets("sheet1").Range("H349,H350,H351,H352,H353,H354,H355,H356,H357,H358").Copy Destination:=Worksheets("Sheet1").Range("G367")
    Worksheets("sheet1").Range("H367,H368,H369,H370,H371,H372,H373,H374,H375,H376").Copy Destination:=Worksheets("Sheet1").Range("G385")
    Worksheets("sheet1").Range("H385,H386,H387,H388,H389,H390,H391,H392,H393,H394").Copy Destination:=Worksheets("Sheet1").Range("G403")
    Worksheets("sheet1").Range("H403,H404,H405,H406,H407,H408,H409,H410,H411,H412").Copy Destination:=Worksheets("Sheet1").Range("G421")
    End Sub

    Kahn Cann

    Monday, August 31, 2015 10:48 PM

Answers

  • I should think that somehow you are identifying the rows to be copied. How are you identifying them?

    Can you set AutoFilter so that it only displays the rows to be copied? Is there a particular cell on the rows that contains unique data? Maybe with a helper column and use a character (Y) to identify the rows and then set the AutoFilter on that column to Y.


    Regards, OssieMac

    Monday, August 31, 2015 11:35 PM
  • >>>I have following code, I want to make it short Could you please help me to make it short.

    Could you provide some information about the rule of Copy?

    Otherwise Worksheet.Range Property returns a Range object that represents a cell or a range of cells. So you could modify your codes like below:

    Sub Copy()
     Worksheets("sheet1").Range("H9:H18").Copy Destination:=Worksheets("Sheet1").Range("G25")
     ...... 
    End Sub

    For more information, click here to refer about Worksheet.Range Property (Excel)

    Tuesday, September 1, 2015 7:20 AM

All replies

  • I should think that somehow you are identifying the rows to be copied. How are you identifying them?

    Can you set AutoFilter so that it only displays the rows to be copied? Is there a particular cell on the rows that contains unique data? Maybe with a helper column and use a character (Y) to identify the rows and then set the AutoFilter on that column to Y.


    Regards, OssieMac

    Monday, August 31, 2015 11:35 PM
  • >>>I have following code, I want to make it short Could you please help me to make it short.

    Could you provide some information about the rule of Copy?

    Otherwise Worksheet.Range Property returns a Range object that represents a cell or a range of cells. So you could modify your codes like below:

    Sub Copy()
     Worksheets("sheet1").Range("H9:H18").Copy Destination:=Worksheets("Sheet1").Range("G25")
     ...... 
    End Sub

    For more information, click here to refer about Worksheet.Range Property (Excel)

    Tuesday, September 1, 2015 7:20 AM