[SOLVED] Copy/Paste skip blanks - Page 5


"Excel VBA Help"

 


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums > Microsoft Topics > Microsoft Excel Forum
Our Info Pages:-
The Code Cage - Microsoft Office help

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

 
 
Thread Tools Rate Thread Display Modes
  #41  
Old 3rd March 2014, 11:43
broro183's Avatar
broro183 broro183 is offline
The Code Cage Staff
MS Office Version: Excel 2007 @ home & 2010 @ work
MS Office Skill Level: Can use VBA
 
Join Date: May 2009
Location: Waikato, New Zealand
Posts: 742
Posts Per Day: 0.24
My Top Tip Count: 0
Thanks to others: 15
Thanked 50 Times in 50 Posts
Reputation: 801 (24) broro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to behold
New_Zealand
Default Re: Copy/Paste skip blanks


------ Register to get rid of these "In Post" ads! ------


hi Sharky,

Here's another approach which merges the use of equations, macros and autofilter. Does this do what you want it to do?

VBA CODE:
Option Explicit 
Sub MovePlus3() 
    Dim LettersWs As Worksheet 
    Dim DatabaseWs As Worksheet 
    Dim AFRng As Range 
    Dim AFDataOnlyRng As Range 
    Dim VisCells As Range 
    Dim LastCll As Range 
    Dim LastRw As Long 
     
    With ThisWorkbook 
        Set LettersWs = .Worksheets("Letters") 
        Set DatabaseWs = .Worksheets("Database") 
    End With 
     
    With LettersWs 
        .AutoFilterMode = False 
         
        Set LastCll = LastCell(.Cells.Parent) 
        LastRw = LastCll.Row 
         
         'ideally the hard-coded column "y" would be removed from the following line.
        With .Range("y2:y" & LastRw) 
            .FormulaR1C1 = _ 
            "=IF(RC[-24]="""",""Column A is blank"",IF(AND(RC[-9]="""",RC[-3]=""""),""both columns P & V are blank"",""Copy this row & then delete it""))" 
            .Calculate 
            .Value2 = .Value2 
        End With 
        Set AFRng = .Range("A1:" & LastCll.Address) 
    End With 
     
    With AFRng 
        Set AFDataOnlyRng = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) 
        If MsgBox("Do you want to delete the rows where column A is blank?", vbYesNo, "Delete Rows...?") = vbYes Then 
            .AutoFilter Field:=25, Criteria1:="Column A is blank" 
            On Error Resume Next 
            Set VisCells = AFDataOnlyRng.SpecialCells(xlCellTypeVisible) 
            On Error Goto 0 
            If Not VisCells Is Nothing Then 
                VisCells.EntireRow.Delete 
            End If 
        End If 
        Set VisCells = Nothing 
         'clear filter
        .AutoFilter Field:=25 
         
        .AutoFilter Field:=25, Criteria1:="Copy this row & then delete it" 
        On Error Resume Next 
        Set VisCells = AFDataOnlyRng.SpecialCells(xlCellTypeVisible) 
        On Error Goto 0 
        If Not VisCells Is Nothing Then 
            With VisCells 
                .Copy Destination:=DatabaseWs.Range(DatabaseWs.Rows.Count, 1).End(xlUp).Offset(1, 0) 
                .EntireRow.Delete 
            End With 
        End If 
        Set VisCells = Nothing 
         'clear filter
        .AutoFilter Field:=25 
    End With 
     
    Set LastCll = Nothing 
    Set AFDataOnlyRng = Nothing 
    Set AFRng = Nothing 
    Set LettersWs = Nothing 
    Set DatabaseWs = Nothing 
     
End Sub 

The above code includes a Call to my "LastCell" function which I've included below. This is just a supporting function but needs to be included in your workbook for the above Sub, MovePlus3, to work.

VBA CODE:
Function LastCell(ws As Excel.Worksheet) As Excel.Range 
     '22/09/2013, RB: written as "Function AttemptAtARobustLastCellFinder_v4(ws As Worksheet) As Range" for:
     'http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28203209.html#a39474286
     'inspired by FP's comments about a "binary chop" approach http://www.experts-exchange.com/Prog...html#a39380467
     'still subject to the limitations of CountA which Qlemo mentioned: http://www.experts-exchange.com/Prog...html#a39380520
     
    Dim PercentArr As Variant 'this can probably be written better
    Dim PercentageMultiplier As Double 
    Dim PercentInd As Long 'percent loop index
    Dim LastRow As Long 
    Dim LastCol As Long 
    Dim RowsInWs As Long 
    Dim ColsInWs As Long 
    Dim LoopInd As Long 
    Dim UpperLim As Long 
    Dim BlockSizer As Long 
    Dim FirstRowOfUsedRng As Long 
     
    With ws 
        RowsInWs = .Rows.Count 
        ColsInWs = .Columns.Count 
    End With 
    PercentArr = Array(0.5, 0.3, 0.1, 0.05, 0.03, 0.01, 0.005, 0.003, 0.001, 1) 
     
     'run a loop to find the last row
     'v4, amended in case the first row of the used range is not Row 1.
    With ws.UsedRange 
        UpperLim = Application.WorksheetFunction.Min(RowsInWs, .Cells(1, 1).Row - 1 + .Rows.Count) 
    End With 
     
    For PercentInd = LBound(PercentArr) To UBound(PercentArr) 
         
        PercentageMultiplier = PercentArr(PercentInd) 
        If PercentageMultiplier <> 1 Then 
            BlockSizer = PercentageMultiplier * RowsInWs 
        Else 
            BlockSizer = 1 
        End If 
         
        For LoopInd = UpperLim To 1 Step -BlockSizer 
            If (LoopInd - BlockSizer + 1) > 0 Then 
                If Application.CountA(ws.Range(LoopInd - BlockSizer + 1 & ":" & LoopInd)) Then 
                    Exit For 
                End If 
            Else 
                Exit For 
            End If 
        Next LoopInd 
         
        UpperLim = LoopInd 
    Next PercentInd 
     
     'v4: .max is used to allow for empty sheets
    LastRow = Application.WorksheetFunction.Max(1, UpperLim) 
     
     'run a loop to find the last column
     'v4, amended in case the first column of the used range is not column 1.
    With ws.UsedRange 
        UpperLim = Application.WorksheetFunction.Min(ColsInWs, .Cells(1, 1).Column - 1 + .Columns.Count) 
    End With 
    For PercentInd = LBound(PercentArr) To UBound(PercentArr) 
         
        PercentageMultiplier = PercentArr(PercentInd) 
        If PercentageMultiplier <> 1 Then 
            BlockSizer = PercentageMultiplier * ColsInWs 
        Else 
            BlockSizer = 1 
        End If 
         
        For LoopInd = UpperLim To 1 Step -BlockSizer 
            If (LoopInd - BlockSizer + 1) > 0 Then 
                With ws 
                     'Searches entire columns
                     'v4 corrected as per http://www.experts-exchange.com/Prog...html#a39392583
                    If Application.CountA(.Range(.Cells(1, LoopInd - BlockSizer + 1), .Cells(RowsInWs, LoopInd))) Then 
                        Exit For 
                    End If 
                End With 
            Else 
                Exit For 
            End If 
        Next LoopInd 
         
        UpperLim = LoopInd 
    Next PercentInd 
     
     'v4: .max is used to allow for empty sheets
    LastCol = Application.WorksheetFunction.Max(1, UpperLim) 
     
     '    'User feedback for testing
    Debug.Print "Last row = " & LastRow & vbNewLine & "Last column = " & LastCol & vbNewLine & "Address = '" & ws.Name & "'!" & ws.Cells(LastRow, LastCol).Address 
     '    MsgBox "Last row = " & LastRow & vbNewLine & "Last column = " & LastCol & vbNewLine & "Address = '" & ws.Name & "'!" & ws.Cells(LastRow, LastCol).Address
     
    Set LastCell = ws.Cells(LastRow, LastCol) 
End Function 

hth
Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...



The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #42  
Old 3rd March 2014, 11:49
broro183's Avatar
broro183 broro183 is offline
The Code Cage Staff
MS Office Version: Excel 2007 @ home & 2010 @ work
MS Office Skill Level: Can use VBA
 
Join Date: May 2009
Location: Waikato, New Zealand
Posts: 742
Posts Per Day: 0.24
My Top Tip Count: 0
Thanks to others: 15
Thanked 50 Times in 50 Posts
Reputation: 801 (24) broro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to behold
New_Zealand
Default Re: Copy/Paste skip blanks

------ Register to get rid of these "In Post" ads! ------


hi,

I think that the tweak for Simon's code is to change:

VBA CODE:
For i = i To 1 Step -1 
to
VBA CODE:
For i = i To 2 Step -1 
This line of code uses the "i" variable, which was previously defined as the last row of the worksheet, to loop from the bottom to the top ("to 2"). The bottom to top logic is defined by the "step -1" section of the statement.

hth
Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...



The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #43  
Old 3rd March 2014, 12:06
Simon Lloyd's Avatar
Simon Lloyd Simon Lloyd is offline
Administrator
MS Office Version: MS Office 97 - 2013
MS Office Skill Level: Can use most of MS Office
 
Join Date: Aug 2008
Location: Manchester, England
Age: 53
Posts: 11,884
Posts Per Day: 3.51
My Top Tip Count: 11
Thanks to others: 49
Thanked 441 Times in 425 Posts
Reputation: 6660 (194) Simon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond repute
11 Top Tips
Wales
Default Re: Copy/Paste skip blanks

------ Register to get rid of these "In Post" ads! ------


What ^he said
__________________
Regards,
Simon Lloyd
Microsoft Office Help

Want to say thanks? then hit this How to say thanks! button in any post you want to thank
If you find one of my posts helpful then hit this one Helpful Answer in the post that helped most!





  #44  
Old 3rd March 2014, 12:18
sharky12345's Avatar
sharky12345 sharky12345 is offline
Junior Member
MS Office Version: MS Office 97, 2003, 2007,2010
MS Office Skill Level: Some VBA experience
 
Join Date: Oct 2013
Location: UK
Posts: 54
Posts Per Day: 0.04
My Top Tip Count: 0
Thanks to others: 0
Thanked 0 Times in 0 Posts
Reputation: 10 (5) sharky12345 is on a distinguished road
Default Re: Copy/Paste skip blanks

------ Register to get rid of these "In Post" ads! ------


Thanks guys - will try both and report back :-)



  #45  
Old 8th March 2014, 10:43
sharky12345's Avatar
sharky12345 sharky12345 is offline
Junior Member
MS Office Version: MS Office 97, 2003, 2007,2010
MS Office Skill Level: Some VBA experience
 
Join Date: Oct 2013
Location: UK
Posts: 54
Posts Per Day: 0.04
My Top Tip Count: 0
Thanks to others: 0
Thanked 0 Times in 0 Posts
Reputation: 10 (5) sharky12345 is on a distinguished road
Default Re: Copy/Paste skip blanks

------ Register to get rid of these "In Post" ads! ------


Guys - works a treat, thank you for all of your help.



The Code Cage Advertisment
Advertisement
  #46  
Old 9th March 2014, 10:34
broro183's Avatar
broro183 broro183 is offline
The Code Cage Staff
MS Office Version: Excel 2007 @ home & 2010 @ work
MS Office Skill Level: Can use VBA
 
Join Date: May 2009
Location: Waikato, New Zealand
Posts: 742
Posts Per Day: 0.24
My Top Tip Count: 0
Thanks to others: 15
Thanked 50 Times in 50 Posts
Reputation: 801 (24) broro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to behold
New_Zealand
Default Re: Copy/Paste skip blanks

------ Register to get rid of these "In Post" ads! ------


Great, I'm pleased we could help :-)
__________________
Rob Brockett. Always learning & the best way to learn is to experience...



  #47  
Old 9th March 2014, 10:55
Simon Lloyd's Avatar
Simon Lloyd Simon Lloyd is offline
Administrator
MS Office Version: MS Office 97 - 2013
MS Office Skill Level: Can use most of MS Office
 
Join Date: Aug 2008
Location: Manchester, England
Age: 53
Posts: 11,884
Posts Per Day: 3.51
My Top Tip Count: 11
Thanks to others: 49
Thanked 441 Times in 425 Posts
Reputation: 6660 (194) Simon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond repute
11 Top Tips
Wales
Default Re: Copy/Paste skip blanks

------ Register to get rid of these "In Post" ads! ------


How To Say Thanks!
How to say thanks!

Saying Thanks

If you have found a tip helpful then please click on the button (like the one on the left) to the bottom right of the post to register your appreciation. If you also found a particular post helpful click the green thumbs up button on that post too so others can quickly find a helpful post!

Saying thanks this way or marking as helpful ensures the person whose post you are marking gets reputation points



__________________
Regards,
Simon Lloyd
Microsoft Office Help

Want to say thanks? then hit this How to say thanks! button in any post you want to thank
If you find one of my posts helpful then hit this one Helpful Answer in the post that helped most!





The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

 
The Code Cage at Facebookhe Code Cage at Twitter


Tags
blanks, copy or paste, skip

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

The Code Cage Affilliates



http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 07:46.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft
UA-8135493-1
Cultural Forum | Study at Malaysian University
X vBulletin 3.8.7 Debug Information
  • Page Generation 0.28827 seconds
  • Memory Usage 6,612KB
  • Queries Executed 16 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footerEnd
  • (1)ad_footerStart
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_headerEnd
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_leftcolumn_code
  • (1)ad_navbar_below
  • (4)ad_postbit_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (4)bbcode_code
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (7)helpful_answers
  • (1)helpfulanswers_javascript
  • (1)navbar
  • (3)navbar_link
  • (3)navbar_noticebit
  • (52)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (2)pagenav_pagelink
  • (7)post_thanks_box
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (7)post_thanks_postbit_info
  • (7)postbit
  • (7)postbit_ctoptip
  • (7)postbit_onlinestatus
  • (45)postbit_reputation
  • (7)postbit_wrapper
  • (1)showthread_changepostsowner_link
  • (1)showthread_quickreply_mrgdp
  • (1)showthread_reason_popup
  • (1)spacer_close
  • (1)spacer_open
  • (1)su_form
  • (3)tagbit
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_datastore.php
  • ./includes/class_hook.php
  • ./awcoding/plugins/awc.php
  • ./awcoding/plugins/ds.php
  • ./awcoding/plugins/ss.php
  • ./includes/functions_notice.php
  • ./mobiquo/smartbanner.php
  • ./mobiquo/include/classTTConnection.php
  • ./mobiquo/smartbanner/head.inc.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/vbatags.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php
  • ./includes/functions_misc.php 

Hooks Called:
  • init_startup
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_check_criteria
  • notices_noticebit
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • helpfulanswers_showthreadorpost
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • helpfulanswers_show_normal
  • reputation_power
  • reputation_image
  • bbcode_parse_start
  • postbit_imicons
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • error_fetch
  • pagenav_page
  • pagenav_complete
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete