Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 5th 07, 10:44 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 19
Default Macro to delete rows based on a condition

I am trying to write a macro that will delete rows from a spreadsheet based
on a condition. I pull information from another program into excel for
budget purposes and it throws the company names in the header in column form,
so based on how many companies I pull in the number of rows that will need to
be deleted will be changing. I have text that starts after the companies
names (Trial Balance) and I want this to always start on A7. My question is
how do I tell excel to look for the word Trial Balance in Column A and then
delete everything that comes before it? Any help is greatly appreciated.

  #2   Report Post  
Old September 6th 07, 12:12 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,058
Default Macro to delete rows based on a condition

This sub looks for the words Trial Balance in column A. It will delete all
rows above the found row:

Sub zero()
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
killer = 0

For i = nLastRow To 1 Step -1
If killer = 1 Then
Cells(i, 1).EntireRow.Delete
End If
If Cells(i, 1).Value = "Trial Balance" Then
killer = 1
End If
Next
End Sub

--
Gary''s Student - gsnu200742


"Darrilyn" wrote:

I am trying to write a macro that will delete rows from a spreadsheet based
on a condition. I pull information from another program into excel for
budget purposes and it throws the company names in the header in column form,
so based on how many companies I pull in the number of rows that will need to
be deleted will be changing. I have text that starts after the companies
names (Trial Balance) and I want this to always start on A7. My question is
how do I tell excel to look for the word Trial Balance in Column A and then
delete everything that comes before it? Any help is greatly appreciated.



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
count filtered rows based on condition greater than forty PLVKC Excel Worksheet Functions 1 September 4th 07 07:56 AM
How to delete a data row based on a condition wmc New Users to Excel 4 April 18th 07 02:40 PM
Is there a way to delete a cell value based on a condition? Peanut Excel Discussion (Misc queries) 2 October 2nd 06 09:55 PM
format group of rows based on condition Aaron Excel Discussion (Misc queries) 0 January 10th 06 12:29 AM
how do I delete all rows that match a condition? djhs63 Excel Worksheet Functions 5 March 16th 05 03:55 PM


All times are GMT +1. The time now is 06:57 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017