+ Reply to Thread
Results 1 to 16 of 16

How to create macro with variable number of columns?

  1. 04-01-2020, 12:59 PM #1
    JenniferGibbons1
    JenniferGibbons1 is offline
    Registered User
    Join Date
    04-01-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    8
    Posts
    14

    How to create macro with variable number of columns?

    Hi, I have created a macro, but it is for a worksheet that has variable number of columns. In the code below, B7 will always be the start cell, but D35, could be E35 and so on, how do I deal with this please?
    Please Login or Register  to view this content.
    Last edited by alansidman; 04-01-2020 at 01:09 PM.
    Reply With QuoteRegister To Reply
  2. 04-01-2020, 01:10 PM #2
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,890

    Re: How to create macro with variable number of columns?

    Code Tags Added
    Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  3. 04-01-2020, 01:14 PM #3
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,890

    Re: How to create macro with variable number of columns?

    Remove your current Range statement

    Insert the following in its place

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 04-02-2020, 01:39 PM #4
    JenniferGibbons1
    JenniferGibbons1 is offline
    Registered User
    Join Date
    04-01-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    8
    Posts
    14

    Re: How to create macro with variable number of columns?

    Hi Alan,

    Apologies for breaking the rules on my post, and thanks for amending it for me.

    Jennifer
    Reply With QuoteRegister To Reply
  5. 04-02-2020, 01:45 PM #5
    JenniferGibbons1
    JenniferGibbons1 is offline
    Registered User
    Join Date
    04-01-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    8
    Posts
    14

    Re: How to create macro with variable number of columns?

    The code you provided didn't exactly work for me, it extended down too far. Is it ok for me to attach the worksheet so you can see what I am trying to create?
    Reply With QuoteRegister To Reply
  6. 04-02-2020, 02:05 PM #6
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,890

    Re: How to create macro with variable number of columns?

    To attach a worksheet, read the yellow banner at the top of the page
    Reply With QuoteRegister To Reply
  7. 04-02-2020, 02:08 PM #7
    JenniferGibbons1
    JenniferGibbons1 is offline
    Registered User
    Join Date
    04-01-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    8
    Posts
    14

    Re: How to create macro with variable number of columns?

    I also have a range of 
    Please Login or Register  to view this content.
    - how would I write that with fixed rows and variable columns please?

    Hi, please can you advise how to do this, and I think I can work the rest out on my own!
    Thanks
    Jennifer
    Last edited by JenniferGibbons1; 04-06-2020 at 05:01 AM.
    Reply With QuoteRegister To Reply
  8. 04-02-2020, 02:42 PM #8
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: How to create macro with variable number of columns? 

    Please Login or Register  to view this content.
    *Cells(Row#,Column#)
    Last edited by protonLeah; 04-02-2020 at 02:46 PM.
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  9. 04-02-2020, 03:08 PM #9
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,890

    Re: How to create macro with variable number of columns?

    Thanks Ben. Didn't realize I had posted it backwards.
    Reply With QuoteRegister To Reply
  10. 04-03-2020, 12:40 PM #10
    JenniferGibbons1
    JenniferGibbons1 is offline
    Registered User
    Join Date
    04-01-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    8
    Posts
    14

    Re: How to create macro with variable number of columns?

    Thanks for that. I have attached what my worksheet should look like and also the code I have so far. Any comments would be greatly appreciated.
    Last edited by JenniferGibbons1; 04-06-2020 at 12:12 PM.
    Reply With QuoteRegister To Reply
  11. 04-06-2020, 11:51 AM #11
    JenniferGibbons1
    JenniferGibbons1 is offline
    Registered User
    Join Date
    04-01-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    8
    Posts
    14

    Re: How to create macro with variable number of columns?

    I also have a range of 

    Please Login or Register  to view this content.
    - how would I write that with fixed rows and variable columns please?
    Reply With QuoteRegister To Reply
  12. 04-06-2020, 12:00 PM #12
    JenniferGibbons1
    JenniferGibbons1 is offline
    Registered User
    Join Date
    04-01-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    8
    Posts
    14

    Re: How to create macro with variable number of columns?

    I think I have it figured out! 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  13. 04-06-2020, 12:13 PM #13
    JenniferGibbons1
    JenniferGibbons1 is offline
    Registered User
    Join Date
    04-01-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    8
    Posts
    14

    Re: How to create macro with variable number of columns?

    Ok one last question please. How do I update this code to allow for variable columns please?
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  14. 04-06-2020, 03:15 PM #14
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: How to create macro with variable number of columns? 

    Please Login or Register  to view this content.
    -2 because you are starting from column C not A
    Reply With QuoteRegister To Reply
  15. 04-06-2020, 03:42 PM #15
    JenniferGibbons1
    JenniferGibbons1 is offline
    Registered User
    Join Date
    04-01-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    8
    Posts
    14

    Re: How to create macro with variable number of columns?

    Thank you, thank you. I love excel!
    Reply With QuoteRegister To Reply
  16. 04-06-2020, 05:20 PM #16
    jolivanes
    jolivanes is offline
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,528

    Re: How to create macro with variable number of columns?

    Re: 
    Please Login or Register  to view this content.
    How would I write that with fixed rows and variable columns please?
    As you quoted but also
    Please Login or Register  to view this content.
    Note: Selecting should just about never be a requirement.
    Google for instance
    Why not using "select" in vba.
    You should get lots of articles that'll explain why.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Using excel vba to create an if formula with variable columns
    By adles in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-05-2019, 05:57 PM
  2. macro to create a table. Number of columns/rows unknown
    By taylorsm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-08-2017, 10:50 AM
  3. [SOLVED] Copy multiple columns x number of times based on variable number?
    By nobodyukno in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-19-2017, 04:13 PM
  4. Macro to cut and paste a variable number of rows to a variable location?
    By TheClaw2323 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-03-2015, 07:57 AM
  5. Macro to insert a variable number of rows into a variable location
    By TheClaw2323 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-08-2015, 05:31 AM
  6. sorting columns based on row criteria, with variable number of columns
    By bardobhb in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-02-2014, 04:02 PM
  7. [SOLVED] Macro to create columns by referencing a variable column
    By mactoolsix in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-11-2014, 05:19 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1