Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old December 20th, 2011, 10:48 AM
Friend of Wrox
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default VBA code to create duplicate lines


I was wondering if anyone could help me with a vba macro i am trying to build.

Currently I have a workbook with table which looks like this:

Row2 1

However I need to macro make the table look like this:

Row1 1
Row2 1

What I need the macro to do is say if the cells in columns 2-4 are empty then delete the row. If the cells in columns 2-4 only have one cell with data ignore it. If multiple cells in columns 2-4 only have data then create a new line so that each number on that row has its own line.

I have managed to create a piece of code which looks at whether the row is empty however I can seem to find a way of doing the rest. The code i have created needs to have variables for the row number as this is not always the same. Currently the code i have is:

    StartLine = Sheets("Sheet1").Range("B24").Value
    EndLine = Sheets("Sheet1").Range("B25").Value

    For i = EndLine To StartLine Step -1

    Cells(i, 9).Select

    If Cells(i, 9) = "" Then
        If Cells(i, 10) = "" Then
        If Cells(i, 11) = "" Then
        If Cells(i, 12) = "" Then
        If Cells(i, 13) = "" Then
            Cells(i, 13).EntireRow.Delete
        End If
        End If
        End If
        End If
    End If
Can anyone help me??



Last edited by jeskit; December 20th, 2011 at 10:58 AM..

Similar Threads
Thread Thread Starter Forum Replies Last Post
lines of code nandar_hayhay C# 5 April 8th, 2009 12:09 AM
How can i create VBA code to Insert a Module? stylin99 Excel VBA 1 January 18th, 2008 10:01 PM
Read Excel Lines VBA gbvdh VB How-To 0 February 26th, 2007 08:44 AM
how to create duplicate error message??? elleetan Access 1 September 16th, 2003 10:47 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.