Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Deleting Sections in a tab

  Asked By: Cory    Date: Oct 06    Category: MS Office    Views: 770
  

I am very new to VBA excel VBA macro.
I do a weekly report and there are 4 tabs. In each tab there are 3
sections - "week", "quarter to date", and "eight weeks". Anyway, at the
beginning of every quarter I need to delete information with regards to
quarter to date because the quarter to date is the same as the week
info.

I thought of doing it by rows, unfortunately, they differ in each tab
on where the row starts and ends. However the beginning of the section
there is the word quarter to date, so I could say like find quarter to
date but telling it to end 2 rows before "eight weeks". How do I do
that? I try writing using record macro and I was not successful. Please
help.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Alyssa Kelley     Answered On: Oct 06

Yes, I think you are correct in that recording macros will not give you what
you want. But they can give you the building blocks you need to make a more
dynamic routine.

Try recording the finds for your keywords; also manually select the rows  to
delete and delete  them while recording.

You don't need to change a lot to make it dynamic. You'll need to store the
row number associated with the first find  and also the row  number associated
with the second find. These are simple integer variables set equal to the
row attribute of the active cell after each find.

You'll then need to substitute those row numbers (plus or minus whatever you
need to avoid deleting  the keyword rows) into the range part of the delete
statement (or its preceding "select").

So, in a test sheet, I recorded

Option Explicit

Sub Macro1()
Range("A1").Select
Cells.Find(What:="quarter to date", After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Cells.Find(What:="eight weeks", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("A16:A36").Select
Selection.EntireRow.Delete
Range("A1").Select
End Sub

Add two variables to hold the rows, and change the row range select before
the delete ...

Option Explicit

Sub Macro1()
Range("A1").Select
Cells.Find(What:="quarter to date", After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Dim FirstRow As Integer
FirstRow = ActiveCell.Row
Cells.Find(What:="eight weeks", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Dim LastRow As Integer
LastRow = ActiveCell.Row
Range("A" & FirstRow + 2 & ":A" & LastRow - 2).Select
Selection.EntireRow.Delete
Range("A1").Select
End Sub

Note that this is not the "nicest" VBA code in the world, but it's easy to
do.

If you want to do it to four sheets, I suggest recording yourself doing it
on two sheets. This will show you how you would go about changing from
sheet to sheet and you can easily expand it to cover the other ones. (Nb it
is "sheet" or "worksheet" - not "tab".)

 
Didn't find what you were looking for? Find more on Deleting Sections in a tab Or get search suggestion and latest updates.




Tagged: