Macro to delete & format ranges

Microsoft Office 2007 professional (aca...
May 25, 2010 at 01:25:19
Specs: Excel 2007
Hello everyone

I have i template sheet for project analysis that contains a table with the below structure

-Table range is B6 :E47
-Range B6:E6 is headers and Range B47:E47 is summation
-Range B7:E46 is blank ( 40row,4 columns)
I past data in the blank rows, the data are always in 4 columns but with variable no. of rows
If i pasted 30 rows, i set fill color to white and set text size to 8 and then delete 10 blank rows
Then i search for the row that contains text ( sales ) assume it's in row 15, i set range ( B15:E15 ) to color 65536

This process is repeated for every new project
I need a macro so i just paste the data and the macro make all the above functions
Can this be done?


See More: Macro to delete & format ranges

Report •

#1
May 25, 2010 at 12:46:46
Try this code in a backup copy of your template.

Since Macros can not be undone, I'd hate to ruin anything you've already written.

Note: This Macro was written in Excel 2003. There is no color 65536, so I used fill color 3 - Red.

If there really is a Fill color of 65536 in 2007, just change that line.

Let me know how this works for you.

Option Explicit
Sub FormatPaste()
Dim blankRow As Integer
Dim mySales As Variant
'Loop through B46 to B7
 For blankRow = 46 To 7 Step -1
'Delete blank rows
  If Cells(blankRow, 2) = "" Then
   Cells(blankRow, 2).EntireRow.Delete
    Else:
'Format remaining rows
     With Range("B7:E" & blankRow)
       .Font.Size = 8
       .Interior.ColorIndex = 2
 'Find text "Sales"
         Set mySales = .Find("Sales", lookat:=xlWhole)
 'Format range where Sales was found
          Range("B" & mySales.Row & ":E" & mySales.Row).Interior.ColorIndex = 3
    End With
 'We're done
    Exit For
  End If
 Next
End Sub



Report •

#2
May 25, 2010 at 23:50:40
Hello Derbydad

Thanks for your support
it's work but not properly because may be i wasn't that clear in some data

First : for the delete
I have another data one the right of that table so i don't need to delete all the row
I have formulas in column A that reads from B
I want to delete range A;E only

Second for the format :

1-For the word "sales" it's a text inside another text so it may be " 51000000 locals sales" or " 51000001 export sales " or other types
All column B text starts with " " then 8 numbers then text
So i use In column A formula Left(B,4) which gives me " " + first two numbers in column B and i build on it other data on the right
you can use " 51" instead of "sales" as it's a common characters between all types of sales
so you can search for " 51" in column A and set the color of the corresponding row B:E
2- For the row of sales B:E i want to format the text color not the fill so i need to keep the fill white

Hope i made it more clear this time

Thanks again for your support


Report •

#3
May 25, 2010 at 23:51:54
Hey

I just want to add that i want to delete and shift cells up

thanks


Report •

Related Solutions

#4
May 26, 2010 at 05:18:01
re: "it's work but not properly because may be i wasn't that clear in some data"

Very true.

"Delete row" is not the same as "delete range"

"Format the text color" is not the same as "i set range ( B15:E15 ) to color 65536"

As I'm sure you can see, you need to be very specific when detailing your requirements. We can't see your spreadsheet, nor can we read minds.

For this solution, I am assuming that the word "sales" appears somewhere within your B:E table, and only appears once - because you said: "Then i search for the row that contains text ( sales )"

Dim mySales As Variant
'Loop through B46 to B7
 For blankRow = 46 To 7 Step -1
'Delete blank rows
  If Cells(blankRow, 2) = "" Then
   Range("B" & blankRow & ":E" & blankRow).Delete shift:=xlShiftUp
    Else:
'Format remaining rows
     With Range("B7:E" & blankRow)
       .Font.Size = 8
       .Interior.ColorIndex = 2
 'Find text "Sales"
         Set mySales = .Find("Sales", lookat:=xlPart)
 'Format range where Sales was found
          Range("B" & mySales.Row & ":E" & mySales.Row).Font.ColorIndex = 3
    End With
 'We're done
    Exit For
  End If
 Next
End Sub


Report •

#5
May 26, 2010 at 05:50:10
Dear DerbyDad03

The Marco worked effectively and exactly match my needs
I also tailored it and used it in another workbook

Sorry again for inconvenience i didn't mean to not be specific and i'll make sure to avoid this next time
Thanks for your support


Report •

#6
May 26, 2010 at 05:59:28
Glad to have been of assistance.

Report •

#7
May 26, 2010 at 23:51:38
Dear DerbyDad03

I was wondering if the above macro can be used for the "current selection" instead of a "range B7:B46"
i have another worksheet that have more than one table and it's ranges are variable
i need to run the macro after i past the data in the table and may be highlight all the table (pasted data +blank rows)

Can this be done??


Report •

#8
May 27, 2010 at 08:08:46
re: i have another worksheet that have more than one table and it's ranges are variable

Variable in what way?

Is it still B:E with only the row locations changing or do columns and/or the width of the various tables change also?


Report •

#9
June 6, 2010 at 01:08:31
Dear DerbyDad03
first sorry for late reply,

The fixed ranges that i have is the columns which is B:E and O:Q
The row is the variable range for me


Report •

#10
June 6, 2010 at 11:08:35
Are you always deleting B:E and O:Q at the same time and with the same numbers of "variable" rows or might you need to delete rows/format those 2 tables separately?

Report •

#11
June 7, 2010 at 05:42:04
i'm deleting them separatly
Please ignore range O:Q in the current time becaue i'll make some changes in this range
Just work on B:E and as the first time i just want to delete range B:E not all the row
Thanks

Report •

#12
June 14, 2010 at 07:33:32
Hello DerbyDad03

i don't know if my inquiry will work or not

but are there any new?
Thaks


Report •

Ask Question