Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Automate deletion process

How do I write a function or macro that will look at what is in a cell,
validate it, if correct delete the row, and if invalid just move down a row
and continue until the end of data in a column?
--
Jan M
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 527
Default Automate deletion process

The following macro looks for duplicates accross cols a, b, and c then
deletes the duplicate rows. It will give you an idea to to work on.

Regards
Peter

"JanM" wrote:

How do I write a function or macro that will look at what is in a cell,
validate it, if correct delete the row, and if invalid just move down a row
and continue until the end of data in a column?
--
Jan M

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Automate deletion process

I'm sorry, but I don't see the macro.

Thanks,
Jan
--
Jan M


"Billy Liddel" wrote:

The following macro looks for duplicates accross cols a, b, and c then
deletes the duplicate rows. It will give you an idea to to work on.

Regards
Peter

"JanM" wrote:

How do I write a function or macro that will look at what is in a cell,
validate it, if correct delete the row, and if invalid just move down a row
and continue until the end of data in a column?
--
Jan M

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 527
Default Automate deletion process

Oops - sorry Jan

Sub DelDupes()
Dim i As Long, nr As Long, col As Integer, nc As Integer
Dim c, d As String, tmp
Range("A1").Select
nr = Range("A1").CurrentRegion.Rows.count
nc = Range("A1").CurrentRegion.Columns.count
'copy data to top row of each person
For i = nr To 2 Step -1
tmp = i
c = Cells(i, 1) & Cells(i, 2) & Cells(i, 3)
d = Cells(i - 1, 1) & Cells(i - 1, 2) & Cells(i - 1, 3)
If c = d Then
Range(Cells(i, 1), Cells(i, nc)).Delete
End If
Next i
End Sub

Regards
Peter

"JanM" wrote:

I'm sorry, but I don't see the macro.

Thanks,
Jan
--
Jan M


"Billy Liddel" wrote:

The following macro looks for duplicates accross cols a, b, and c then
deletes the duplicate rows. It will give you an idea to to work on.

Regards
Peter

"JanM" wrote:

How do I write a function or macro that will look at what is in a cell,
validate it, if correct delete the row, and if invalid just move down a row
and continue until the end of data in a column?
--
Jan M

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Automate deletion process

Jan

Sub Delete_Valid_Rows()
findstring = "valid" 'substitute your validation parameter here
Set B = Range("A:A").Find(What:=findstring, LookAt:=xlWhole)
While Not (B Is Nothing)
B.EntireRow.Delete
Set B = Range("A:A").Find(What:=findstring, LookAt:=xlWhole)
Wend
End Sub


Gord Dibben MS Excel MVP

On Wed, 13 Jun 2007 09:00:03 -0700, JanM wrote:

How do I write a function or macro that will look at what is in a cell,
validate it, if correct delete the row, and if invalid just move down a row
and continue until the end of data in a column?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I automate the process of copying the first letter of a ce Steve Excel Worksheet Functions 1 March 28th 07 02:49 AM
Time to process CASBMS Excel Discussion (Misc queries) 0 February 27th 07 09:41 PM
If/then Process? Zane B Stein Excel Discussion (Misc queries) 1 December 20th 05 04:27 PM
Automate process via button on form kjg Excel Worksheet Functions 0 November 30th 05 11:01 PM
How to automate process of replacing values? J. Gutierrez Excel Discussion (Misc queries) 5 November 3rd 05 11:29 PM


All times are GMT +1. The time now is 08:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"