Insert Rows and copy data Via VBA Code

Dear Experts,

Kindly find an excel File attached.
I need to insert blank rows below each row based on a formula via VBA Routine
Then only non-blank data needs to be copied from Range1 to Range2

The File will clear everything.

Regards
EEE.xls
Kanwal_No2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TinTombStoneCommented:
Well, this will do the inserting of rows for you, I dont really understand what you want to do next.

Sub PlaceBlankRows()
Dim numRows As Integer

Range("AH3").Select
    Do Until ActiveCell.Value = ""
        numRows = ActiveCell.Value
        For x = 1 To numRows
            ActiveCell.Offset(1).Select
            ActiveCell.EntireRow.Insert
            ActiveCell.Offset(0, -33).Value = ActiveCell.Offset(-1, -33).Value
        Next
        ActiveCell.Offset(1).Select
    Loop

End Sub
0
Kanwal_No2Author Commented:
Hi,

Have you seen the file.
In actual scenario the data is to be copied to Sheet 2.
In the file the resultant data has been given on Sheet1 itself just to facilitate the easy view.

---Range A1:AG10 represent the actual data (The number of rows might be "n")
---Range A13:N46 represent the resultant data. (Off Course here also the number of rows will increase / decrease based on rows in original range)
---Range A2:K10 needs to be expanded based on how many Stock items each Entry contain in Range L2:AG10 for each entry. Here only the Sr will be copied in each row while other data will not be copied.
---Range L2:AG10 is to be transposed to Range L14:N46 in way that only those Item Codes and Item Qty is copied which have some value and the Centre name is copied from L1:AG1 corresponding to each Stock item in L2:AG2.

Hope above coupled with the example file will clear what I need.
0
Patrick MatthewsCommented:
Side note:

Dim numRows As Integer

Never, ever, EVER use Integer for a row-counting variable.  It is an awful habit to get into.

Excel worksheets have up to 65,536 rows in Excel 2003, and a little over 1 MM in Excel 2007 and later.

Integer tops out at 32,767.

People who use Integer to declare variables that have anything to do with rows are sometimes startled to get runtime overflow errors on code that had worked for years and years, only to realize after hours of debugging that it wasn't until you reached Row 32,768 that the code bombed :)

Always, always, always use Long instead of Integer.  It's not the 70s any more, and we don't have to jealously guard every precious byte of memory.

Patrick
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Saqib Husain, SyedEngineerCommented:
OK try this macro

Saqib
Sub transform()
Dim sw As Worksheet
Dim tw As Worksheet
Dim mr As Range
Set sw = Sheets("sheet1")
Set tw = Sheets("sheet2")
Set mr = Range("Myrange")
tw.UsedRange.Offset(1, 0).Clear
tr = 2
For Each rw In mr.Offset(1, 0).Resize(mr.Rows.Count - 1, mr.Columns.Count).Rows

For i = 1 To 11
tw.Cells(tr, i) = rw.Cells(1, i)
Next i
tw.Range(Cells(tr, 1), Cells(tr, 17)).Borders(xlEdgeTop).LineStyle = xlContinuous
tw.Range(Cells(tr, 1), Cells(tr, 17)).Borders(xlEdgeTop).Weight = xlMedium
ttr = tr
For i = 12 To 33
If rw.Cells(1, i) <> "" Then
tw.Cells(tr, 12) = sw.Cells(1, i)
tw.Cells(tr, 13) = sw.Cells(2, i)
tw.Cells(tr, 14) = sw.Cells(rw.Row, i)
If tr > ttr Then
tw.Range(Cells(tr, 1), Cells(tr, 17)).Borders(xlEdgeTop).LineStyle = xlContinuous
tw.Range(Cells(tr, 1), Cells(tr, 17)).Borders(xlEdgeTop).Weight = xlThin
End If
tr = tr + 1
End If
Next i
Next rw
tw.Range(Cells(tr, 1), Cells(tr, 17)).Borders(xlEdgeTop).LineStyle = xlContinuous
tw.Range(Cells(tr, 1), Cells(tr, 17)).Borders(xlEdgeTop).Weight = xlMedium
For i = 1 To 17
Range(Cells(2, i), Cells(tr - 1, i)).Borders(xlEdgeLeft).LineStyle = Cells(1, i).Borders(xlEdgeLeft).LineStyle
Range(Cells(2, i), Cells(tr - 1, i)).Borders(xlEdgeRight).LineStyle = Cells(1, i).Borders(xlEdgeRight).LineStyle
Range(Cells(2, i), Cells(tr - 1, i)).Borders(xlEdgeLeft).Weight = Cells(1, i).Borders(xlEdgeLeft).Weight
Range(Cells(2, i), Cells(tr - 1, i)).Borders(xlEdgeRight).Weight = Cells(1, i).Borders(xlEdgeRight).Weight
Next i
End Sub

Open in new window

0
TinTombStoneCommented:
Why, thank you Mathew.  However the line Dim numRows as Integer was never meant to loop through a potential 65,000 rows.

It was only ever going to have a max value of:  drumroll...22

Of course I could have used a Byte.  But, as it's not the 70's, I didn't bother

You may want to try answering the question rather than picking holes in others efforts
0
Patrick MatthewsCommented:
TinTombStone,

In my opinion, the role of the Expert should be not merely to answer the immediate question at hand, but also to demonstrate best practices when it makes sense to do so.

Best practice in VBA coding is to use Long for any variable that has anything to do with counting or identifying rows.  Using Integer for rows is a bad habit to get into, and when we demonstrate that technique to VBA users who need help, we are doing them a disservice.

For that matter, in my opinion it never makes sense to use Integer in VBA code.  Using Integer can actually make your code slower, albeit by a degree a human is unlikely to notice, because internally the VBA runtime will have to do a bunch of round-trip Integer-to-Long-to-Integer conversions.  The only use case I can think of for using Integer instead of Long is when your application needs to constrain inputs to numbers <= 32,767 (ignoring the negative part of Integer's range for now), but in my opinion forcing, and then trapping, a runtime error is a sub-optimal way to enforce that sort of constraint.

No doubt if you search my comment history you'll find numerous instances of me using Integer.  I don't do that any more, because I learned the hard way that using Integer or Byte is a false economy.

Patrick
0
Kanwal_No2Author Commented:
Thanks a lot Experts,

Just give me some time to apply and test the solution to actual scenarios.

Regards
Kanwal
0
Kanwal_No2Author Commented:
Hi Saqib,

Can you show me where to put the code in the file ?

Regards
Kanwal
0
Saqib Husain, SyedEngineerCommented:
Press Alt-11
goto insert > Module
Paste the code
close this VBA window
press Alt-F8
Select Transform
click on run
0
Kanwal_No2Author Commented:
Dear Saqib,

That looks wonderful again on the face of it. I am trying to understand the code so that I can modify it to suit my requirement.

Regards
Kanwal
0
Saqib Husain, SyedEngineerCommented:
Comments inserted to reduce your stress
Sub transform()
Dim sw As Worksheet
Dim tw As Worksheet
Dim mr As Range
Set sw = Sheets("sheet1")
Set tw = Sheets("sheet2")
Set mr = Range("Myrange")
tw.UsedRange.Offset(1, 0).Clear
tr = 2
For Each rw In mr.Offset(1, 0).Resize(mr.Rows.Count - 1, mr.Columns.Count).Rows
'**********The first row of each block Columns A to K
    For i = 1 To 11
        tw.Cells(tr, i) = rw.Cells(1, i)
    Next i
'**********Thick lines at the top of each block
    tw.Range(Cells(tr, 1), Cells(tr, 17)).Borders(xlEdgeTop).LineStyle = xlContinuous
    tw.Range(Cells(tr, 1), Cells(tr, 17)).Borders(xlEdgeTop).Weight = xlMedium
    ttr = tr
    For i = 12 To 33
'**********The rows of each block columns k to N
        If rw.Cells(1, i) <> "" Then
            tw.Cells(tr, 12) = sw.Cells(1, i)
            tw.Cells(tr, 13) = sw.Cells(2, i)
            tw.Cells(tr, 14) = sw.Cells(rw.Row, i)
            If tr > ttr Then
'**********Thin lines at the top of each row
                tw.Range(Cells(tr, 1), Cells(tr, 17)).Borders(xlEdgeTop).LineStyle = xlContinuous
                tw.Range(Cells(tr, 1), Cells(tr, 17)).Borders(xlEdgeTop).Weight = xlThin
            End If
            tr = tr + 1
        End If
    Next i
Next rw
'**********Thick line at the bottom of the table
tw.Range(Cells(tr, 1), Cells(tr, 17)).Borders(xlEdgeTop).LineStyle = xlContinuous
tw.Range(Cells(tr, 1), Cells(tr, 17)).Borders(xlEdgeTop).Weight = xlMedium
For i = 1 To 17
'**********Vertical lines copied from the header row
    Range(Cells(2, i), Cells(tr - 1, i)).Borders(xlEdgeLeft).LineStyle = Cells(1, i).Borders(xlEdgeLeft).LineStyle
    Range(Cells(2, i), Cells(tr - 1, i)).Borders(xlEdgeRight).LineStyle = Cells(1, i).Borders(xlEdgeRight).LineStyle
    Range(Cells(2, i), Cells(tr - 1, i)).Borders(xlEdgeLeft).Weight = Cells(1, i).Borders(xlEdgeLeft).Weight
    Range(Cells(2, i), Cells(tr - 1, i)).Borders(xlEdgeRight).Weight = Cells(1, i).Borders(xlEdgeRight).Weight
Next i
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kanwal_No2Author Commented:
Thanks Saqib,

That makes a job lot easier.

Regards
Kanwal
0
Kanwal_No2Author Commented:
Dear Saqib,

I understand sw is "Source Worksheet"
tw is "Transformed Worksheet"
mr is "MyRange"
I Doubt rw is used to denote Row
But What is tr ?

I tried to understand the code, but can you please help me understand what the attached code is doing.....

Regards
Kanwal
tr = 2
For Each rw In mr.Offset(1, 0).Resize(mr.Rows.Count - 1, mr.Columns.Count).Rows
'**********The first row of each block Columns A to K
    For i = 1 To 11
        tw.Cells(tr, i) = rw.Cells(1, i)
    Next i

    ttr = tr
    For i = 12 To 33
'**********The rows of each block columns k to N
        If rw.Cells(1, i) <> "" Then
            tw.Cells(tr, 12) = sw.Cells(1, i)
            tw.Cells(tr, 13) = sw.Cells(2, i)
            tw.Cells(tr, 14) = sw.Cells(rw.Row, i)
            If tr > ttr Then

            End If
            tr = tr + 1
        End If
    Next i
Next rw

For i = 1 To 17
Next i

End Sub

Open in new window

0
Saqib Husain, SyedEngineerCommented:
tr is the row number in the transformed sheet
0
Saqib Husain, SyedEngineerCommented:
You will have to identify where you are lost and I shall try to help you.
0
Kanwal_No2Author Commented:
Thanks Saqib,

First of all my purpose is solved and so I won't keep this Question open for too long. Whenever I need some help I will get back here or through a new question.

Regards
Kanwal
0
Kanwal_No2Author Commented:
Thanks Saqib,

That was wonderful. A little more elaboration in the comments would certainly have helped a lot, but I assume you did yours Best within the available time resources.

Thanks again.
0
Saqib Husain, SyedEngineerCommented:
Anything related to this one.... just ask. I am ready to clarify.

Saqib
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.