Can I use "find / replace" to automatically "Insert Rows" in a do.
Have a long column of numbers
Every time I see the number 5, I need to insert two rows. Doing this by
hand is tedious. Can you help me find a shortcut?
The IF function does not do this, right?
Not 25, 35, 5351, just plain old 5?
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
FirstRow = 1
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "C").Value = 5 Then
...Blank cells, hide row
Hide i have a piece of code thanks to some newsgroup
people that i have changed a little to suit my purpose.
Basically i want it to look at ColumnB between rows 32 and
79 inclusive and if it fines blank cells in columnB hide
the entire row. This is what i have and it gives me a
compile error Loop without Do. I don't know if the rest
will run because i can't get past the error.
Your help is appreciated
ApplicationScreenUpdating = False
Do While IsEmpty(ActiveCell)
If IsEmpty(ActiveCell) Then
Se...Apply alternate grey/red shading to selected rows in a table
With any number of rows SELECTED in a table I would like to apply
alternate (grey/red) shading.
Grey: RGB (242, 242, 242)
Red: RGB (239, 211, 210)
How can this be achieved using VBA?
Help is much appreciated.
Thank you very much in advance.
You are not trying now ;)
This is almost the same as the question you asked earlier in the week
Dim i As Long
Dim oRow As Row
i = 1
For Each oRow In Selection.Range.Rows
If i Mod 2 = 0 Then
oRow.Shading.BackgroundPatternColor = RGB(242, 242, 242)
oRow.Shading.Back...formula range doesn't update for new rows
I have a workbook with multiple sheets. Some sheets contain imported data
and some contain sumproduct formulas referencing the data. When the data is
refreshed, the formulas all return #N/A values. The range in the formula is
not being updated to reflect the new rows that have been added when the data
sheets are refreshed. This does not happen consistently, but it is
frustrating. It is easy enough to find and replace the incorrect numbers,
but it repeats the same pattern again when the data is refreshed.
=(SUMPRODUCT((Expense!$B$2:$B$5874=$C$2)*(Expense!$C$2:$C$5874=$C$3)...Getting a useful Auto Scale
I have a chart showing number of calls per employee as a column graph and
duration of calls as a line graph together. The scale is automatic. The scale
for the number of calls works great. The scale for the duration works, but it
doesn't use nice numbers. For example, if my employee with the longest
duration is currently at 01:55:43 (hh:mm:ss), the top of the scale shows
02:09:36. I want to keep it on automatic, but I want it to use nicer numbers,
like 02:00:00 or 02:15:00 instead of 02:09:36. Is there any easy way to do
this? If not, is there any way to do this?
sb1920alk...Hide row and column labels
How can I hide the row and column headings in a
spreadsheet. (the a, b, c, etc. across and the 1, 2, 3,
Tools > Options > View
Remove check from "Row and Column Headers"
Norman Harker MVP (Excel)
How about programmatically?
On Fri, 25 Jun 2004 06:20:20 +1000, "Norman Harker"
>Tools > Options > View
>Remove check from "Row and Column Headers"
Record a macro while you...group & summarize diff rows of data that have something in common
I am trying to arrange a large amount of data (inventory) in 2 different
ways. 1) Group all identical parts into one row and 2) Group all identical
locations into one row. I dont know how to do this. I am dealing with over
3,000 rows. Any suggestions?
Depending on your workbook layout, you may be able to use a pivot table
to summarize the data. There are instructions in Excel's Help, and Jon
Peltier has information and links:
> I am trying to arrange a large amount of data (inventory) in 2 different
&g...Integration Manager destination for Bank Transfers
Currently there is not a way to bring in a mass entry for bank transfers. My
company has atleast 50 every month. WE manually key these in. It would make
sense to have a destination with in integration manager so this could be done
with a click on a button.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click &q...Link columns to rows
There must be an easy way to do this....I just don't know
what it is. I have data in columns on one sheet that I
want to paste as a link to another sheet, but this data
has to be laid out in rows on the second sheet. I thought
selecting the entire paste area would work, but it
doesn't. I could paste the link for each cell
individually, but this would take forever.
You want to transpose, select the column range of data copy it go t
the destination sheet and then select from the EDIT/SPECIAL PASTE
TRANSPOSE. If not try the formula =TRANSPOSE(array). Hope this helps
Fabl...how do I import a csv files to execl and be extrad on clume auto
...Is there a convienent way I can exchange a table's rows and columns?
I'm new to excel. Now I have several tables I wan't to change their rows to
columns and vice versa, how can I do these?
Copy the data you want to transpose. Than click a cell
where you would like to past it yoo and right click,
select paste special then transpose.
Hope it helps
>I'm new to excel. Now I have several tables I wan't to
change their rows to
>columns and vice versa, how can I do these?
Thank you very much!
"Jay" <email@example.com> wrote in message
news:060401c3cdaf$374fbf90$a0012...Auto Formatting the date field from mmddyyyy to mm/dd/yyyy
I have the current script in place to format date fields from
mmddyyyy to mm/dd/yyyy. I am trying to reduce every possible keystroke
and this will eliminate 2. My issue is that CRM fires it's validity
check on the date field before the onchange event. Thus, it finds an
error in the date because it hasn't had a chance to format. Any
suggestions on circumventing the CRM validity check or any other work
// Get the field that fired the event.
var oField = event.srcElement;
// Validate the field information.
if (typeof(oField) != "undefined&...Contract Transfer
Hello - A client wants to transfer contract line items from a contract quote
(created by salesman (in Sales Transaction Entrt / Contract Sales) to an
existing contract. Currently this transfers to a new contract, but the
client wants to maintain a single Hardware contract and update line items
when clients purchase more items. This is being updated manually at present
and client wants to automate information flow from sales to contract
administration. Could someone suggest how I could achieve this? Thanks in
...Transfer from Checking to Loan
I am looking for assistance with an existing loan in Money. I recently
changed the way I pay the loan and noticed the loan account shows no payments
since I started this new payment. I used to pay the loan directly from my
checking account. Now, I consolidated several loan and credit card payments
into one monthly payment. The service I use sends the money to the loan
company for me. When creating the monthly payment to the service, and
breaking it up into categories, there doesn't appear to be a way to designate
a payment to the loan. Any suggestions?
A Loan Payment is a spl...Mnthly auto ,shift schedule for multi employee
I am incharge of multi employee and need to schedule monthly shift duty as:
Weekday (Monday to friday)-1730-2300
weekend ( saturday-sunday and public holiday) two shift 0830-1530 1st shift
1530-2300 2nd shift.This schedule I need to plan every month and must be
equal to every employee. Please advise so that it feed auto to spreadsheet
and equal to every employee.
I have been able to get a button that inserts rows but also need th
formula in the new row to copy from the row moved.
Below is my code for inserting rows. What would I add to have th
formulas across the row to copy to the new row??
Message posted from http://www.ExcelForum.com
David McRitchie has some code you can steal at:
&...How do I setup auto reply in MS Outlook Professional?
That is my entire quesiton. Thanks in advance.
"sheana" in <news:6D77A8E0-BD83-43A5-8B98-B5965BF7C774@microsoft.com>
> That is my entire quesiton. Thanks in advance.
No, it is not your entire question. You left out the details, like why
you want to leave your computer always powered on, Windows always
loaded, Outlook always loaded, and rely on a not as reliable client-side
rule rather than use an auto-responder up on your mail host or at your
They body of a post is to provide details but you chose not to provide
any, like WHICH version of Outlook ...From Rows to column
Hello, need some help here.
I need to change data from:
Cell Cell1 Cell2 Cell3 Cell4 Cell5 Cell6 Cell7
A A1 A2 A3 A4 A5 A6 A7
B B1 B2 B3 B4 B5 B6 B7
C C1 C2 C3 C4 C5 C6 C7
D D1 D2 D3 D4 D5 D6 D7
E E1 E2 E3 E4 E5 E6 E7
And turn them into
You could copy your cells, then paste special and select transpose,
then select each column and drag underneath the first column. How many
rows are in your spreadsheet? If there are many, you may need a
for the 35 data points you have here
I assume...Deleting Rows 04-20-10
I want to delete two rows - one row has the words "Employee Name" in the
first column, and the row before it. This for-next loop removes the row with
For Lrow = LastRow To FirstRow Step -1
If .Cells(Lrow, "A").Value = "Employee Name" Then _
Any suggestions how to delete the row with "Employee Name" and the one
Here is one way, without a lot of re-write.
For Lrow = LastRow To FirstRow S...Delete cells in Excel 2007 give a pop up: delete entire sheet row?
I just want to delete the cells and shift to the left but it doesn't
give me that option. Why?
On Jan 15, 1:51=A0pm, frogman7 <frogm...@googlemail.com> wrote:
> I just want to delete the cells and shift to the left but it doesn't
> give me that option. =A0Why?
I found out some interesting info. The cells in question did not have
any filters on them but the sheet did have a few cells that had
filtering on them that is why I got the delete entire sheet row? pop
up. I took the filter off of the few cells on the sheet and tried
again and it worked.
...how do you hide letter/numbers column/row?
Basically as it says in the subject.
How do you hide the numbers and letters in grey - your column/row
"paul thomas" <firstname.lastname@example.org> wrote in message
> Hi all
> Basically as it says in the subject.
> How do you hide the numbers and letters in grey - your column/row
Tools>Options>View. Uncheck "row...Auto Fill Options #6
Until today, the default of my auto fill options was copy cells; in addition,
I never saw the auto fill box, ever. I've disabled the show paste and show
insert options so the box doesn't appear but it defaults to fill series and
adds a day to each date I drag and drop. Holding down the control key
overrides the fill series default but I'd like to go back to copy cells as
the default. Can it be done?
I think it depends on what you have selected.
If I put 1 in A1, select just A1 and drag that fill handle down, excel copies
If I put 1 in A1 and 2 in A2, select A1:A2 a...Turn off auto-reply?
My son recently activated a "vacation message" feature on
Outlook Express that automatically replied to all emails
with a message notifying the sender that we were on
vacation. However, now he doesn't remember how he turned
it on. There is no "out of office assistant" option
under tools, and there aren't any message rules set up or
activated. Please help, everyone who emails me is still
receiving this message, which is very embarrassing for
I don't know if this is what your son did. But in Outlook Express as far as
my limited knowledge...auto reply mail system
I have MSoffice 2003 with a normal e-mail account. I want to set auto reply
mail system when I am absent, but the system says that I have to be connected
to teh exchange system. How do I do that keeping my actual mail accounts?
Anyone who could help?
Check with your ISP. They may offer this option for you.
Be advised that if you were to setup an auto-reply routine on your home
computer, it would have to be running 24/7 while you were gone and there's a
good likelihood that it would be validating your address to any spammers that
got through to your system in your absence.
...Subtotal and Auto Filter
I have a worksheet that lists total sales for the month for 8 locations.
I have subtotaled the sheet so I can autofilter by location, vendor, class of product, sales rep, etc.
I send each location only their section monthly, they can filter by any column except of course location.
I haven't been able to figure out how to separate out each location into a separate file, maintaining all the other filters.
Time runs short, so I end of saving the file 8 times, then delete the rows that don't belong to that location.
It's cumbersome, and I know it's not the best way.
What should ...