Results 1 to 6 of 6

Thread: [Excel] Macro for counting blank cells then using the result

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2012
    Posts
    3

    [Excel] Macro for counting blank cells then using the result

    Hi all,

    I hope you can help me with this. It's complicated for me cause I'm still new to making VB Macros.

    I've attached the excel file with 2 worksheets for your reference.

    The 'Data' sheet is a sample of the data that I need to represent as per the format shown in the 'Requested Final Format' sheet.

    Now, making a macro that copies and then transpose-paste the data is easy. But since the count of 'Design No' aren't constant, I need to figure out how to count the number of designs linked to that particular 'bale' and just copy-paste that selection.

    I figure one way would be to count the number of blank cells from the 'Bale' column, and use that to loop the copy paste commands.

    If anyone can think of a better way, please let me know.

    Any help would be appreciated.

    Thank you!!
    Attached Images Attached Images   

  2. #2
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: [Excel] Macro for counting blank cells then using the result

    welcome to the forum.
    do u need unique design no?
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  3. #3

    Thread Starter
    New Member
    Join Date
    Jun 2012
    Posts
    3

    Re: [Excel] Macro for counting blank cells then using the result

    I need to arrange the data I’m given to a pre designed format.
    Figure one (the vertical standing attached image) is the data I must use.
    Figure two is the pre designed format.

  4. #4
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: [Excel] Macro for counting blank cells then using the result

    if the Bale wil not repeat, u can Loop until cell is not blank.
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  5. #5
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: [Excel] Macro for counting blank cells then using the result

    Try stepping through this code to get an idea of how to know "how far to go" with each bale's data, and when to be done with the process. My workbook with the data is "yrich1.xls."

    Code:
    Sub reformat()
        Dim lastBaleRow As Integer
        Dim i As Integer    'first row of data set per bale
        Dim j As Integer    'last row of data set per bale
        Dim strRange As String
        lastBaleRow = Workbooks("yrich1.xls").Sheets("sheet1").Range("a65536").End(xlUp).Row
        i = 1
        While i <= lastBaleRow
            i = Workbooks("yrich1.xls").Sheets("sheet1").Range("a" & i).End(xlDown).Row
            If i <> lastBaleRow Then
                j = Workbooks("yrich1.xls").Sheets("sheet1").Range("a" & i).End(xlDown).Row - 1
            Else
                j = Workbooks("yrich1.xls").Sheets("sheet1").Range("b65536").End(xlUp).Row
            End If
            strRange = "b" & i & ":c" & j   'just my example, would be "b1:c4"
            Workbooks("yrich1.xls").Sheets("sheet1").Range(strRange).Select 'can use COPY here
        Wend
    End Sub

  6. #6

    Thread Starter
    New Member
    Join Date
    Jun 2012
    Posts
    3

    Re: [Excel] Macro for counting blank cells then using the result

    Thank you vbfbryce!

    It works great! Exactly what I needed!

    Thanks again!

Posting Permissions

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



Click Here to Expand Forum to Full Width