How to write macro VBA code for excel sheet to generate a report.

Question

1.00/5 (1 vote)

See more:

Actually i have an excel sheet something like this.

Feature 	Epic	User Story	Task/Defect
F1			
	        EP1		
		             US11	
			                    T11
			                    D11
			                    D12
		             US12	
			                    T21
			                    T22
	        EP2		
		             US21	
			                    T21
			                    D21
			                    D22
		             US22	
			                    T3
			
F2                    			
	        EP4		
		              US1	
			                    T4

But i need to write VBA code which will give output something like below

Feature 	Epic	User Story	Task/Defect
F1	         EP1	    US11	       T11
F1	         EP1	    US11	       D11
F1	         EP1	    US11	       D12
F1	         EP1	    US12	       T21
F1	         EP1	    US12	       T22
F1	         EP2	    US21	       T21
F1	         EP2	    US21	       D21
F1	         EP2	    US21	       D22
F1	         EP2	    US22	       T3
F2	         EP4	    US1	           T4

Please help me. Thanks in advance.

What I have tried:

I would like to change tree view structure to normal tabular format as decribed above.

Posted 26-Feb-18 20:54pm

shibashish mohanty

Updated 26-Feb-18 21:50pm

v3

Add a Solution

Comments

OriginalGriff 27-Feb-18 3:03am

What have you tried?
Where are you stuck?
What help do you need?

[no name] 27-Feb-18 3:05am

I am new to Macro VBA. So could you please help me. Thanks

OriginalGriff 27-Feb-18 3:17am

Help you do what? We have no idea what you are talking about!
This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with. And we have no idea what you have tried, and where you need help with this.

[no name] 27-Feb-18 3:45am

i have described that i have four columns but in row my records are in Tree View format. I need to change the format as single line tabular format as i have given one sample data. Please let me know if you need more details.

Richard MacCutchan 27-Feb-18 4:11am

You just need to start at the top cell (e.g F1), select all the empty cells below and use "Fill down". Use the macro recording feature of Excel to create the basic VBA code.

[no name] 27-Feb-18 4:32am

Thanks for your reply.This is what i need but this solution will work for static data. but if you will change the record it won't work.

Richard MacCutchan 27-Feb-18 5:00am

You can save macros and use them in different spreadsheet files. But that always assumes that the format of the data remains constant. You can find quite a few websites that specialise in Excel programming, which are probably more appropriate than CodeProject.

[no name] 27-Feb-18 5:28am

Thanks. But previously i have found very helpful solutions from code project. so i have posted here but after getting answer from OriginalGriff , it seems Freelancer is best for developers who needs help immidiately. So we need to feed peanuts to get monkeys.

Richard MacCutchan 27-Feb-18 5:38am

I think perhaps you misunderstand his comments. This site is here to help people with code they have written, not to write it for them. And it is also primarily for the mainstream programming languages (C, C++, C#, Java etc.), whereas VBA is specific to Microsoft Office applications - which is why I suggested other sites.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2018-02-27T09:58:00

Assuming that data are stored in columns A-D and starts from row 2 (forst row = headers), check this:

VB

Option Explicit


Sub ExplodedDataToTable()
    Dim srcWsh As Worksheet
    Dim i As Long, r As Long

On Error GoTo Err_ExplodedDataToTable

    Set srcWsh = ThisWorkbook.Worksheets(1) 'you can pass the name of worksheet
    i = 2
    r = srcWsh.Range("D" & srcWsh.Rows.Count).End(xlUp).Row
    
    Do While i < r
        'remove empty row
        If srcWsh.Range("A" & i) = "" And srcWsh.Range("B" & i) = "" And _
            srcWsh.Range("C" & i) = "" And srcWsh.Range("D" & i) = "" Then
            srcWsh.Range("A" & i).EntireRow.Delete xlShiftUp
            r = r - 1
            i = i - 1
            GoTo SkipNext
        End If
        'A is not empty
        If srcWsh.Range("A" & i) <> "" And srcWsh.Range("B" & i) = "" And _
            srcWsh.Range("C" & i) = "" And srcWsh.Range("D" & i) = "" Then
            srcWsh.Range("B" & i & ":D" & i).Delete xlShiftUp
            r = r - 1
            i = i - 1
            GoTo SkipNext
        End If
        'A & B is not empty
        If srcWsh.Range("A" & i) <> "" And srcWsh.Range("B" & i) <> "" And _
            srcWsh.Range("C" & i) = "" And srcWsh.Range("D" & i) = "" Then
            srcWsh.Range("C" & i & ":D" & i).Delete xlShiftUp
            r = r - 1
            i = i - 1
            GoTo SkipNext
        End If
        'A, B & C is not empty
        If srcWsh.Range("A" & i) <> "" And srcWsh.Range("B" & i) <> "" And _
            srcWsh.Range("C" & i) <> "" And srcWsh.Range("D" & i) = "" Then
            srcWsh.Range("D" & i).Delete xlShiftUp
            r = r - 1
            i = i - 1
            GoTo SkipNext
        End If
        'A, B & C is empty, D is not empty
        If srcWsh.Range("A" & i) = "" And srcWsh.Range("B" & i) = "" And _
            srcWsh.Range("C" & i) = "" And srcWsh.Range("D" & i) <> "" Then
            srcWsh.Range("A" & i - 1 & ":C" & i - 1).Copy srcWsh.Range("A" & i)
            GoTo SkipNext
        End If
        'A, B is empty, C & D is not empty
        If srcWsh.Range("A" & i) = "" And srcWsh.Range("B" & i) = "" And _
            srcWsh.Range("C" & i) <> "" And srcWsh.Range("D" & i) <> "" Then
            srcWsh.Range("A" & i - 1 & ":B" & i - 1).Copy srcWsh.Range("A" & i)
            GoTo SkipNext
        End If
        'A, is empty, B, C & D is not empty
        If srcWsh.Range("A" & i) = "" And srcWsh.Range("B" & i) <> "" And _
            srcWsh.Range("C" & i) <> "" And srcWsh.Range("D" & i) <> "" Then
            srcWsh.Range("A" & i - 1).Copy srcWsh.Range("A" & i)
            GoTo SkipNext
        End If
        
SkipNext:
        i = i + 1
    Loop
    

Exit_ExplodedDataToTable:
    On Error Resume Next
    Set srcWsh = Nothing
    Exit Sub

Err_ExplodedDataToTable:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_ExplodedDataToTable

End Sub

OriginalGriff · Answer 2 · 2018-02-26T21:49:00

It doesn't quite work like that.
We do not do your work for you.
If you want someone to write your code, you have to pay - I suggest you go to Freelancer.com and ask there.

But be aware: you get what you pay for. Pay peanuts, get monkeys.

The idea of "development" is as the word suggests: "The systematic use of scientific and technical knowledge to meet specific objectives or requirements." BusinessDictionary.com[^]
That's not the same thing as "have a quick google and give up if I can't find exactly the right code".
So either pay someone to do it, or learn how to write it yourself. We aren't here to do it for you.

How to write macro VBA code for excel sheet to generate a report.

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0