-
Jun 2nd, 2012, 04:09 PM
#1
Thread Starter
New Member
[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!!
-
Jun 2nd, 2012, 09:59 PM
#2
Re: [Excel] Macro for counting blank cells then using the result
welcome to the forum.
do u need unique design no?
-
Jun 3rd, 2012, 04:27 AM
#3
Thread Starter
New Member
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.
-
Jun 3rd, 2012, 04:44 AM
#4
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.
-
Jun 3rd, 2012, 08:03 AM
#5
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
-
Jun 3rd, 2012, 11:48 AM
#6
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|