VBA MAcro help - using a variable starting point

**Sadievan** · 03-26-2015, 03:21 PM

I have a macro that is used to clean up a 268+ page text document. The macro deletes all of the repetitive page headers by using the Filter function and then deleting the non-essential rows. Each month the text file is a diffeent length and the starting row to be deleted once the filtering has been done, varies. Is there a way to be non-specific on which row to start the deleting process.

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.AutoFilter
ActiveSheet.Range("$A$7:$E$20000").AutoFilter Field:=1, Criteria1:=Array( _
" ", "ANNUAL", "DATE:", "INV NO. I", "MONTHLY", "QUARTERLY", "SEMI-ANNU", "SUBSIDIAR", "SUPP", _
"SUPPLIER", "TIME:", "TOTA", "TOTAL DUE", "="), Operator:=xlFilterValues
Range("A48").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
Range("B5").Select

The Range "A48" is where I have the issue. The "A" will always be constant but the "48" will vary each month.

Thx

**mikeTRON** · 03-26-2015, 06:32 PM

You can use logic instead to find that point, yes.

So my question would then be, how do YOU determine where to start that?

**Sadievan** · 03-26-2015, 07:51 PM

The starting point is determined by the first row after the filtering occurs. If the first row below the column headers is row 48, then everything is ok. If it is less then row 48, then the macro doesn't work properly for the rows above row 48. It works ok for everything below row 48. Thx

**Sadievan** · 03-27-2015, 08:13 AM

I'm new at VBA. Any suggestions on how to write the commands for this process?. Thx

**mikeTRON** · 03-27-2015, 10:37 AM

If it is ALWAYS row 48, then where is the disconnect?

**Sadievan** · 03-27-2015, 10:57 AM

Sorry for the confusion. When the macro was first created, the start row happened to be 48. However, since then, each month the start row has changed. Last month it was 37, the month before it was row 51. It is always the first row below the column headers when the filter is applied.

**mikeTRON** · 03-27-2015, 11:10 AM

Sorry, you need to post a sample workbook, otherwise I ca'nt help much.I have no idea what a column header looks like for you so I cant help you use VBA to find that cell.

What I would do with a sample workbook, is follow your guidance to determine WHAT is the starting point, then translate that into VBA. I cant determine what that is in excel, just words in the forum, which makes it divvidult for me to help you translate into VBA, if that makes sense.

If you can easily identify the starting point manually, I think we should be able to do it pretty easily with VBA...

**Sadievan** · 03-27-2015, 12:06 PM

Hi. I am receiving an error message when I try to upload my sample file. I will try from a different computer over the weekend. Thanks for your help so far.