Copy selected rows to new sheet

Microsoft Microsoft excel 2007 open lice...
May 2, 2010 at 00:30:03
Specs: Windows 7 64
I want to copy 2 columns (Account number, Value) to a new sheet. Removing:
1. Blank lines
2. Lines with special caracters
3. Remove Non numeric caracters from account numbers ( ABC12345).

See More: Copy selected rows to new sheet

Report •

May 2, 2010 at 04:07:48

As those who respond are all volunteers, it is customary to ask for help, rather than just state what you want.

That aside, you do need to include more details.

1. Include the names of the worksheets (source and destination)
2. Include the column letters for the two columns
3. Describe what you mean by lines with special characters.
a. List the special characters.
b. Can these characters appear in either cell in the row, or are they just in one column - if so which one.
c. Are the special characters on their own, or can they be embedded in other words/numbers.
d. Include some examples

From what you have said so far, to move the data from one worksheet to another and at the same time excluding blank rows and certain other rows based on 'special characters' as well as removing non-numeric data from the account numbers, will require a custom written macro.
Certainly possible, but a lot more information will be required to do it.


Report •

May 2, 2010 at 08:11:51
Thanks for the reply.
1. The source sheet is InputSheet and destination TB.
2. The 2 columns are A and D.
3. Special caracteris a "-" These caracters only appear in column A and are ontheir own.
The columns looks like:
12345 342.56
34567 765.87

76543 9876.34
CA4321 234.56

The result should only show:
12345 342.56
34567 765.87
76543 9876.34
4321 234.56

Report •

May 3, 2010 at 07:07:18

Here is a macro that takes your data on a worksheet named "InputSheet" and copies data in columns A and D only if column A does not start with a "-".
Blank rows are ignored - based on the cell in column A being empty.
The data in column D is copied 'as is'
The data in column A is copied 'as is' if it is a number, but if not a number, any number is extracted from the cell and copied.
The copying is to a worksheet named "TB" and is to columns A and D

The start row on both worksheets is set at row 2 but can be changed in the code in these lines:

'set start of source data
Set rngSrcStart = Worksheets("InputSheet").Range("A2")
'set start of data in destination worksheet
Set rngDestStart = Worksheets("TB").Range("A2")

To run this macro I suggest you add a button to your source worksheet ("InputSheet")
From the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)

In Developer - Controls select Insert and choose the button icon.
Draw the button on the worksheet
In the 'Assign Macro' dialog box select 'New'

In the code window that opens enter this:

Option Explicit

Sub Button1_Click()
Dim rngSrcStart As Range
Dim rngSrcEnd As Range
Dim rngCell As Range
Dim rngDestStart As Range
Dim intDestOffset
Dim strNumber As String
Dim n As Integer

On Error GoTo ErrHnd:

'stop screen updating to speed processing and remove flicker
Application.ScreenUpdating = False

'set start of source data
Set rngSrcStart = Worksheets("InputSheet").Range("A2")
'find end of source data
Set rngSrcEnd = Worksheets("InputSheet").Range("A" & CStr(Application.Rows.Count)) _

'set start of data in destination worksheet
Set rngDestStart = Worksheets("TB").Range("A2")
'set destination row offset counter
intDestOffset = 0

'loop through all rows on the Input sheet
For Each rngCell In Worksheets("InputSheet").Range(rngSrcStart, rngSrcEnd)
    'test that column A is not empty or does not starts with "-"
    If Not (rngCell.Text = "" Or Left(rngCell.Text, 1) = "-") Then
        'move data in column A
        'test if column A is a number
        If IsNumeric(rngCell.Value) Then
            'its a number - so move it
            rngCell.Copy Destination:=rngDestStart.Offset(intDestOffset, 0)
            'get the numeric part - start at the end
            For n = Len(rngCell.Text) To 1 Step -1
                If IsNumeric(Mid(rngCell.Text, n, 1)) Then
                    strNumber = Mid(rngCell.Text, n, 1) & strNumber
                End If
            Next n
            'move the number
            rngDestStart.Offset(intDestOffset, 0).Value = strNumber
        End If
        'move data in column D
        rngCell.Offset(0, 3).Copy _
                    Destination:=rngDestStart.Offset(intDestOffset, 3)
        'increment destination row offset
        intDestOffset = intDestOffset + 1
    End If
Next rngCell

'reinstate screen updating
Application.ScreenUpdating = True
Exit Sub

'error handler
'reinstate screen updating
Application.ScreenUpdating = True
End Sub

Note that Sub Button1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Sub Button1_Click().

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.

Right click the button and Edit the name to something meaningful

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

After selecting any cell, the new command button should now respond to a click and run the macro.


Report •

Related Solutions

May 3, 2010 at 08:27:45
Thanks very much, it worked perfectly. I appreciate jour help.

Report •

May 3, 2010 at 09:18:55
You're welcome

and thanks for the feedback



Report •

Ask Question