exclude zero or empty two blank cells together for the same row when copy data

**MKLAQ** · 05-05-2023, 04:19 PM

Hi guys
I want copying data for columns C,D,E,F and last three columns PU,SA,BL with ignore empty or zero for two cells together in the same row for columns PU,SA . so every week I will add new three columns PU,SA,BL should deal with last three columns PU,SA,BL because will change in location when matching the headers , also should update second sheet based on change first sheet.
notice : should exclude TOTAL row from this condition totally .
I want create whole report as I did it in second sheet with the formatting and borders .

thanks

**Marc L** · 05-05-2023, 08:15 PM

According to your attachment a starter Excel basics VBA demonstration to paste only to the SH1 worksheet module :

PHP Code:


Sub Demo1() 
         Me.Next.UsedRange.Clear 
    With UsedRange.Columns 
         [XFC2].Formula = "=(C2<>""TOTAL"")*SUM(" & .Cells(2, .Count - 2).Resize(, 2).Address(0) & ")" 
        .AdvancedFilter 1, [XFC1:XFC2] 
         [XFC2].Clear 
         Union(.Item("A:D"), .Item(.Count - 2).Resize(, 3)).Copy Me.Next.[A1] 
    End With 
         If FilterMode Then ShowAllData 
End Sub

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

**MKLAQ** · 05-06-2023, 03:10 AM

thanks !
just odd case occurs for last item CRF25 if this item doesn't copy to second sheet because of it contains empty or zero cells and try modify by add numeric values to copy to second sheet in reality will copy but also repeat copying item in column A without repeat copy D:F and PU,SA,BL

**Marc L** · 05-06-2023, 08:45 AM

Always works as expected on my side whatever the case, as with the Excel basics advanced filter
it is very not possible to 'repeat' anything so I can't guess what could be the bad on yours !

**MKLAQ** · 05-06-2023, 08:58 AM

just write numeric value except zero in P2 and look what happens.

**Marc L** · 05-06-2023, 09:39 AM

As I wrote « according to your attachment » so as always your bad 'cause you have modified the OUPUT layout ‼

Post #2 demonstration updated just removing the useless column A …

**jindon** · 05-06-2023, 09:55 AM

Originally Posted by MKLAQ

Hi guys
I want copying data for columns C,D,E,F and last three columns PU,SA,BL with ignore empty or zero for two cells together in the same row for columns PU,SA .

Please Login or Register  to view this content.

**MKLAQ** · 05-06-2023, 10:32 AM

@Mac L I expect the serial numbers in column A as I did it in OP.

**MKLAQ** · 05-06-2023, 10:34 AM

@jindon
thanks , can also insert column A ITEM and auto numbers based on data has copied ,please?

**Marc L** · 05-06-2023, 10:41 AM

Originally Posted by MKLAQ

@Mac L I expect the serial numbers in column A as I did it in OP.

The reason why my original starter procedure was keeping the useless column A but you delete it in your last attachment ‼
As you already got the way to auto numbering a column in your previous threads - it's time to read and learn ! -
so I let you do the 'cosmetics', the 'useless' …
Anyway thanks for the rep' !

**jindon** · 05-06-2023, 10:43 AM

Please Login or Register  to view this content.

**MKLAQ** · 05-06-2023, 11:11 AM

As you already got the way to auto numbering a column in your previous threads

I know .
you said

According to your attachment a starter Excel basics VBA demonstration to paste only to the SH1 worksheet module :

so you missed based on my attachment

it's time to read and learn !

thanks for your advice
it's not problem about autonumbering , but when somebody write the code I don't think hard to do that.
just to make the matter is completed

thanks for your code

**MKLAQ** · 05-06-2023, 11:14 AM

@jindon
I appreciate for your effort & time
The code works efficiently without any deficiencies

thanks very much

exclude zero or empty two blank cells together for the same row when copy data

LinkBack

Thread Tools

Rate This Thread

Display

exclude zero or empty two blank cells together for the same row when copy data

Hi, try this !

Re: exclude zero or empty two blank cells together for the same row when copy data

Re: exclude zero or empty two blank cells together for the same row when copy data

Re: exclude zero or empty two blank cells together for the same row when copy data

Re: exclude zero or empty two blank cells together for the same row when copy data

Re: exclude zero or empty two blank cells together for the same row when copy data

Re: exclude zero or empty two blank cells together for the same row when copy data

Re: exclude zero or empty two blank cells together for the same row when copy data

Re: exclude zero or empty two blank cells together for the same row when copy data

Re: exclude zero or empty two blank cells together for the same row when copy data

Re: exclude zero or empty two blank cells together for the same row when copy data

Re: exclude zero or empty two blank cells together for the same row when copy data

Thread Information

Users Browsing this Thread

Similar Threads

Dynamic Chart - How to exclude data tin blank cells

Transpose and Manipulate Data - Exclude Blank cells

Exclude Empty Cells in a Data Validation List

Selection of loop data, move copy and paste data, and exclude blank rows

look for empty/blank cells and copy data from another cell where the first cell....

Counting data in non blank cells and exclude certain data from the count

How to exclude cells that are empty?

Bookmarks

Bookmarks

Posting Permissions