Macro to delete entire row


"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
  #1  
Old 12th May 2010, 10:48
Carol's Avatar
Carol
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Macro to delete entire row
     Excel: VBA   


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

Category: Excel: VBA   



Hi,

I am using this formula

=IF(COUNTIF($A$6:$A$33100,A6)=1,TRUE,COUNTIF($A$6:A6,A6)=COUNTIF($A$6:$A
$33100,A6))

to highlight the last row item for cells with duplicates. I need to
delete the entire row of all rows that were not highlighted by this
formula.

Hope this makes sense. Any help would be appreciated.

*** Sent via Developersdex http://www.developersdex.com ***



The Code Cage Advertisment
Advertisement
  #2  
Old 12th May 2010, 12:38
Javed's Avatar
Javed
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Macro to delete entire row

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


On May 12, 2:46 pm, Carol <anonym...@devdex.com> wrote:
> Hi,
>
> I am using this formula
>
> =IF(COUNTIF($A$6:$A$33100,A6)=1,TRUE,COUNTIF($A$6:A6,A6)=COUNTIF($A$6:$A
> $33100,A6))
>
> to highlight the last row item for cells with duplicates. I need to
> delete the entire row of all rows that were not highlighted by this
> formula.
>
> Hope this makes sense. Any help would be appreciated.
>
> *** Sent via Developersdexhttp://www.developersdex.com***


You can use any loop code but if you change the formula a bit then it
can be done very easily----

probably your function returns FALSE and highlights the row.Change
that formula so that 1 returns.

Worksheets("SheetName").Range
_("Coulmn_Where_formula_Placed").Specialcells(type:=xlcelltypeformulas,value:=XlNumbers).Entirerow.Delete



  #3  
Old 12th May 2010, 15:20
helene and gabor's Avatar
helene and gabor
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Macro to delete entire row

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


Hello Carol,

I am puzzled over the formula that you are using.
If you have Excel 2007 then you can frame your column A (A6 to last line)
and
data
remove duplicates

Best Regards,

Gabor Sebo

"Carol" <anonymous@devdex.com> wrote in message
news:%23qh2Mgb8KHA.3276@TK2MSFTNGP02.phx.gbl...
>
>
> Hi,
>
> I am using this formula
>
> =IF(COUNTIF($A$6:$A$33100,A6)=1,TRUE,COUNTIF($A$6:A6,A6)=COUNTIF($A$6:$A
> $33100,A6))
>
> to highlight the last row item for cells with duplicates. I need to
> delete the entire row of all rows that were not highlighted by this
> formula.
>
> Hope this makes sense. Any help would be appreciated.
>
> *** Sent via Developersdex http://www.developersdex.com ***
>





The Code Cage Advertisment
Advertisement
  #4  
Old 12th May 2010, 15:59
helene and gabor's Avatar
helene and gabor
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Macro to delete entire row

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


Hello Carol,

This formula if entered in A7( not in A6) and down will show all second,
third, etc. duplicates you want to get rid of.

=IF(AND(COUNTIF($A$6:$A$33600,A7)>1,COUNTIF($A$6:A6,A7)>=1),"delete","")

Best Regards,


Gabor Sebo


"Carol" <anonymous@devdex.com> wrote in message
news:%23qh2Mgb8KHA.3276@TK2MSFTNGP02.phx.gbl...
>
>
> Hi,
>
> I am using this formula
>
> =IF(COUNTIF($A$6:$A$33100,A6)=1,TRUE,COUNTIF($A$6:A6,A6)=COUNTIF($A$6:$A
> $33100,A6))
>
> to highlight the last row item for cells with duplicates. I need to
> delete the entire row of all rows that were not highlighted by this
> formula.
>
> Hope this makes sense. Any help would be appreciated.
>
> *** Sent via Developersdex http://www.developersdex.com ***
>





  #5  
Old 13th May 2010, 00:47
helene and gabor's Avatar
helene and gabor
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Macro to delete entire row

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


Hello Carol,

the enclosed program deletes all lines where column J says False.

Sub deleterowiftext()
Dim i As Long
' TO BE CATEGORIZED" in column J
Count = 0
For i = Cells(Rows.Count, "J").End(xlUp).Row To 1 Step -1
'MsgBox "cells (i,j)" & Cells(i, "j")

If Cells(i, "j") = "False" Then Count = Count + 1
If Cells(i, "j") = "False" Then Rows(i).Delete
If Cells(i, "j") = "False" Then MsgBox "i count" & i & Count
Next i
MsgBox "count is " & Count
' number of deleted lines.
End Sub

--------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------


"Carol" <anonymous@devdex.com> wrote in message
news:%23qh2Mgb8KHA.3276@TK2MSFTNGP02.phx.gbl...
>
>
> Hi,
>
> I am using this formula
>
> =IF(COUNTIF($A$6:$A$33100,A6)=1,TRUE,COUNTIF($A$6:A6,A6)=COUNTIF($A$6:$A
> $33100,A6))
>
> to highlight the last row item for cells with duplicates. I need to
> delete the entire row of all rows that were not highlighted by this
> formula.
>
> Hope this makes sense. Any help would be appreciated.
>
> *** Sent via Developersdex http://www.developersdex.com ***
>





The Code Cage Advertisment
Advertisement
 
The Code Cage at Facebookhe Code Cage at Twitter


Tags
delete, entire, macro, row

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 05:32.


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.18204 seconds
  • Memory Usage 6,360KB
  • Queries Executed 19 (?)
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
  • (3)ad_postbit_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (5)helpful_answers
  • (1)helpfulanswers_javascript
  • (1)navbar
  • (3)navbar_link
  • (3)navbar_noticebit
  • (52)option
  • (5)post_thanks_box
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (5)post_thanks_postbit_info
  • (5)postbit
  • (5)postbit_ctoptip
  • (5)postbit_wrapper
  • (1)showthread_changepostsowner_link
  • (1)showthread_extra_fields
  • (1)showthread_quickreply_mrgdp
  • (1)showthread_reason_popup
  • (1)spacer_close
  • (1)spacer_open
  • (1)su_form
  • (4)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
  • ./includes/functions_prefix.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
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • error_fetch
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete