deleting blanks and commas

FUGMAN · #16 03-01-2017, 09:36 PM

this how I get the data from sheet one to sheet 2

Dim sht2 As Worksheet
Set sht2 = Sheets("Sheet 2")

Selection.EntireRow.Select

With Selection
.Copy Destination:=sht2.Range("A2")
End With

FUGMAN · #17 03-01-2017, 09:51 PM

what I think would work is if I could define the following destination line of code to read

.Copy Destination:=sht1.Range("A2") but with ("A2") modified to reflect col A of the active row sheet one that was inserted

NoSparks · #18 03-01-2017, 10:10 PM

yes, the sheet 1 activecell.row is the right row number to copy from, insert and paste to.

FUGMAN · #19 03-01-2017, 10:22 PM

Where I am hitting a brick wall is how to express a range statement (syntax)that equates to .Range("col A - ActiveCell.row")

NoSparks · #20 03-01-2017, 11:33 PM

There is only ever one active cell at a time in Excel and it is always on the active sheet so have to make sure sheet 1 is the active sheet to use this

Code:

' copy back to sheet 1

With Sheets("Sheet1")
    .Activate           '<~~ makes sure right sheet is active
    .Rows(ActiveCell.Row).Insert Shift:=xlDown
    Sheets("Sheet2").Range("A3:Z3").Copy .Range("A" & ActiveCell.Row)
End With

Won't be back 'til morning.

FUGMAN · #21 03-02-2017, 01:26 PM

NoSparks.
Sorry for not getting back sooner. I crashed and just recently woke up.
Works like a charm. My stumbling block was resolved by ***Range("A" & ActiveCell.Row)***

Thanks again ever so much. Hopefully nothing else will raise its ugly head, but I should be so lucky. Now I am hoping to put this all to work. Thanks again. Your assistance has been invaluable.

FUGMAN · #22 03-06-2017, 03:13 PM

I'm back...things are working really good but I have a small enhancement that I would like to implement.

The following code that NoSparks provided me inserts and copies the indicated range ("A3:Z3") above an existing ActiveCell.Row.…and then the newly inserted row becomes the active row.
With Sheets("CCCCCCC")
.Activate '<~~ makes sure right sheet is active
.Rows(ActiveCell.Row).Insert Shift:=xlDown
Sheets("DATA ENTRY").Range("A3:Z3").Copy .Range("A" & ActiveCell.Row)
End With
I would like to accomplish the same thing except to have the Range("A3:Z3") inserted below the existing ActiveCell.Row.(and then have the newly inserted row becomes the ActiveCell.Row).

NoSparks · #23 03-06-2017, 03:52 PM

Code:

With Sheets("CCCCCCC")
    .Activate '<~~ makes sure right sheet is active
    ActiveCell.Offset(1, 0).Select  'move 1 row down, 0 columns over
    .Rows(ActiveCell.Row).Insert Shift:=xlDown
    Sheets("DATA ENTRY").Range("A3:Z3").Copy .Range("A" & ActiveCell.Row)
End With

Standing by for next revision

FUGMAN · #24 03-06-2017, 04:11 PM

NoSparks...THANKS ALOT....This project started out about a month ago with all manual actions. With implementation of macros I have been able to reduce the time required to accomplish my mission a significant amount. What would have taken an hour manually has been reduced to about 6 minutes incorporating VBA and you have contributed greatly to this increase in efficiency.

NoSparks · #25 03-06-2017, 04:30 PM

But..... you're still doing things one row at a time. Maybe that's a necessity, I don't know.
I've been expecting you to ask how to automate doing the same manipulation for every original row all at once.
Is that yet to come ?

FUGMAN · #26 03-06-2017, 05:59 PM

One row at a time is the nature of the beast. . However, as it is, I am looking at about 150 to 200 hours of work. That would have been 1500 plus without the vba being incorporated. I can live with 200 hours.

FUGMAN · #27 03-17-2017, 08:31 AM

NoSparks....I am back with yet another "final" request.
If you are so inclined, I would like a code that would eliminate duplicate records. However, I want the eliminated duplicates to be dumped into a separate worksheet called dupesheet. Thanks in advance for any help.

NoSparks · #28 03-17-2017, 10:05 AM

Well.... this "final" request requires some clarification.

What determines a record to be a duplicate? Every cell of the row being the same, or just certain columns?
Do you or are you able to sort the data so the duplicates are together?
Which of the duplicates should be moved to the dupesheet?
How, when, why would you know this macro should run?

And after that, what's causing the duplicates and can they be prevented?

FUGMAN · #29 03-17-2017, 10:48 AM

NICE TO HEAR FROM YOU AGAIN.
A duplicate record is one that is identical to another. All cells in a row matching all cells in another row. I have about 5000 records (rows of data) and it was very difficult for my feeble brain to remember that I captured the data for a record previously. The consequence is that I find that there are dupes.

Yes the records can be sorted to have the dupes grouped...
but manually removing dupes is like picking fly poop out of pepper and subject to missing some. Macro is a quality control measure.

After eliminating the dupes from the main sheet, the eliminated records to go to the dupesheet. ie...a record "aaa" might show 3 times. Maintain the original worksheet with one record "aaa" and the other 2 to be sent to dupesheet.

I know there are dupes. The macro will be a one time deal to get rid of those dupes.
Any subsequent revisions to the data will be seldom and magnitude of changes quite simple to manage.

NoSparks · #30 03-17-2017, 10:04 PM

Providing your data is sorted so the dupes are together this should do what you're asking.
Be sure to test on a copy of the file.

Code:

Sub RemoveDubeRows()
    Dim lr As Long, r As Long, wr As Long
Application.ScreenUpdating = False
'row to write to
wr = Sheets("dupesheet").Cells(Rows.Count, 1).End(xlUp).Row + 1
'rows to check for dupes
With Sheets("Sheet1")
    'last row
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    'start at bottom row and work up
    For r = lr To 2 Step -1
        If .Cells(r, 1) = .Cells(r - 1, 1) Then
            'compare the two rows
            '  credit Tim Williams ~~~ with great explanation at
            '  http://stackoverflow.com/questions/19395633/how-to-compare-two-entire-rows-in-a-sheet
            If Join(Application.Transpose(Application.Transpose(.Cells(r, 1).EntireRow.Value)), "") = _
               Join(Application.Transpose(Application.Transpose(.Cells(r - 1, 1).EntireRow.Value)), "") Then
                'copy and delete if equal
                .Cells(r, 1).EntireRow.Copy Sheets("dupesheet").Cells(wr, 1)
                .Cells(r, 1).EntireRow.Delete
                wr = wr + 1
            End If
        End If
    Next r
End With
Application.ScreenUpdating = True
End Sub

I'll never use pepper again

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
pivot not showing number format with commas	olybobo	Excel	1	05-05-2016 05:09 PM
Counting Blanks in 1 Column and Non-Blanks in Another	dogwood705	Excel	4	02-07-2015 08:45 AM
Commas within fields in .csv files	gar	Excel	1	01-29-2015 09:24 PM
How to use mid or left functions to seperate city,st,zip with no commas	sinaranje	Excel	4	11-11-2014 10:52 PM
No inverted commas in word 2013.	tonycrossley	Word	3	01-27-2014 04:55 PM