Hiding blank rows
I have a spreadsheet with columns, first name, last name, home phone,
business phone and cell phone. When I don't have phone information in any of
the three columns I would like that column to be hidden or deleted. Can I do
this in some automated fashion? Thanks. Michael
try adding a column
in that column put something like
and use autofilter to hide the 1s
> I have a spreadsheet with columns, first name, last name, home phone,
> business phone and cell phone. When I don't have phone information in any of ...Can you lock a drop-down cell so that it sorts with the row?
I have created drop-down cells (type of business) for my client contact list
but when I sort that list alphabetically or by last contacted date the
drop-down cells don't sort with it. How can I solve that issue?
Thanks for the responses!
Are these Data Validation dropdown lists?
Where is the list range located?
If inside your sort area the lists should change to whatever is in A2:A20
after the sort.
Tested in 2003 and 2007
Gord Dibben MS Excel MVP
On Tue, 26 Jan 2010 13:19:01 -0800, Lisa in Victoria <Lisa in
Victoria@discussions.micros...Macro for merging rows
I have a fairly large spreadsheet that are sorted based on a file # (ie:
E0800100, E0800101). The spreadsheet is setup to where each entry is on an
individual row as seen below:
A B C
E0800100 Review.... 1.0 (hr)
E0800100 Review.... 2.0
E0800101 Review.... 1.5
E0800102 Review.... .5
I am trying to organize the spreadsheet so that there is only one row per
file number and the Descriptions (B) and Time (C) extend along the columns of
A. B....Set date range and filter results via form/menu for Report Preview
I created a form to use as a "print menu" for a report. You can select
the date range (txtStartDate and txtStopDate). I have this part of the
code working. However, I also need to be able to filter based on the
job flag: [Flag] with value of 1, 2 or 3-- OR show all (still applying
the date range). I created a list box on the form and named it
[ReportFilter]. It's bound to the first column (hidden) with 0 for
All, 1, 2 and 3 to correspond with the Flag names. I can't figure out
how to get All records to show on top of not being able to incorporate
with the above date range.
H...To find the Last row
The following is the pattern of the data I have.
I need to know which is the last row which has the data
A1 - AAA
A3 - <blank>
A4 - CCC
A5 - DDD
A6 - <Blank>
A7 - <Blank>
A8 - <Blank>
A9 - EEE.
In the above example I want to get the row number as 9 as the last row
which has the data.
Can this be acheived by some formula. I tried COUNTA, but it does not
serve my purpose.
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.Excel...Row Limit in Excel
I work a lot with excel and I know that the row limit is 65,536, but I need
more than that. Is there any way to make the number of rows infinite or at
least to give me a certain amount of more rows. If there is please let me
know. Thank you.
Molly, that is all there is, per sheet.
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
"Molly" <firstname.lastname@example.org> wrote in message
news:FD2C7921-E249-4...looking for empty row to paste a range of copied cells
Hi - I am trying to make a code that will copy a range ("A27:L27") on sheet 1
and then look for the next empty row on sheet 2 and paste it in range
("A27:L27") . I would also like the macro to insert a new blank row (or
insert the copied row) for the purpose of shifting existing SUM functions on
sheet 2 down. I would like those functions to be right below the
copied/pasted cells every time the macro is executed.
Thanks for any help - Jim A
You don't Mention What column you want to sum
This code will copy and paste to the fist row and then sum column D
Sub Cop...Row colouring that accommodates filtering?
What can be used instead of "=MOD(ROW(),2)=1" that accommodates filtering,
The above gives clumps of identically-coloured rows, dependent on the
particular filter criteria used.
Thank you! :oD
As long as there are no empty cells within the filtered list:
Assume A1:B1 is the header row.
A2:B10 is the data
Select the range A2:B10
Formula Is: =MOD(SUBTOTAL(3,$A1:$A$2),2)=0
"StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message
> What can be used instead of "=MOD(ROW...Insert row with formatting from row below instead of row above?
My default Excel template has the top row formatted as a header, with
bold text and a dark bottom border to distinguish it from the rows
So if I insert a row at row 2, the new row is formatted like the
header. Then I have to select the new row and remove the bold font,
remove the dark border, select the header row and reapply the bottom
Is there a way I can change the insert default so that it takes it's
formatting from the row below instead of the row above. Or better
yet, so that it has no formatting at all?
AFAIK you can't change this behaviour
Fra...calculating row by row
I would like to have Excel total the sum of a row 5 of randomly generated
numbers in a 6th column, one column at a time.
I have no problem generating the random numbers and totalling them but I
would like to generate the first number and place it in the total column then
when I initiate it generate the second number, add this to the first and
place it in the total and so on to the 5th column. Is this possible?
Unless I'm missing something here, this formula should do it for you. This
is for row 2 on the sheet, and would go into cell F2
That formula will &q...Sorting Rows by Color
Excel 2003: Is there a way to sort rows by color? Put all
the blue rows together, all the green together, etc?
There is but you need to work it.
Take a look at
(remove nothere from the email address if mailing direct)
"SharonJo" <email@example.com> wrote in message
> Excel 2003: Is there a way to sort rows by color? Put all
> the blue rows together, all the green together, etc?
I am currently working with Visual Studio 2008 and asp.net 3.0 I have
the following question I hope that someone can help me with:
1) I have a GridView with rows that I read from a database.
2) The user can select a row from the grid, which fires the event:
protected void MyGrid_SelectedIndexChanged(object sender,
<update text field on form>
3) I have the <update text field on form> portion of the routine
working, but need to call the <cal...EXcluding Zeros from the average in a row
I am trying to average a row of numbers (F35:U35) that have numeric zeros in
some of the cells. However, I would like to exclude them, and the cells from
the calculation "=AVERAGE(F35:U35)". Is there a way to do that?
This array formula will do the job:
To be entered with <Shift><Ctrl><Enter> instead of <Enter>,
also if edited later.
Followup to newsgroup only please.
"Geo" <Geo@discussions.microsoft.com> skrev i en meddelelse
news:9C2B0B65-1AF...Flag row if cell values = something specific
Let's say A1 = top
and B1 = Bottom
in C1, I want to say that if A1 = top and B1 = bottom then the cell
background color of C1 should be red.
How can I do this?
And I need to do this in a macro. I can't use conditional formating from the
I have a recorded macro that does all my formatting and I need to add this.
"Some Dude" <firstname.lastname@example.org> wrote in message
> Let's say A1 = top
> and B1 = Bottom
> in C1, I want to say that if A1 = top and B1 = bottom then the cell
> background color of...Row and columns
In excel my rows are numbered 1 through XXX and my
columns are also numbered 1 through XXX. On my other
computers using the same current version of Excel the
columns are identified using Alpha A,B,C,D etc. Anyone
know how to change the columns to Alpha v.s. numbers?
Joe, tools, optins, general, and uncheck R1C1 reference style
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
"Joe" <email@example.com...I selected row 1 and row 2 to freeze and it is freezing at row 12
I am selecting row 1 and row 2 to freeze and excel keeps freezing at row 12?
Select one row only, if you want 2 rows above where it freezes select row 3,
that will leave row1 and 2 unaffected when you scroll down
"Lori Brooks" <Lori Brooks@discussions.microsoft.com> wrote in message
>I am selecting row 1 and row 2 to freeze and excel keeps freezing at row
To freeze row 1 & 2 select A3 and then Window|Freeze Panes
"Lori Broo...Renaming catalogs/rows/trees in FRX
Does anyone know why the Rename option in FRX is greyed out. I have used
this before to rename existing catalogs/rows/columns. We are on FRX 6.7.
...Delete Rows #2
I would like to be able to delete a row with a name in it based on a
cell with that name from another cell in a different worksheet. Any
This example loop through row 1 - 100 on the activesheet and if the value in A is the same as in
Sheets("Yoursheet").Range("A1").Value in delete the row
Maybe you can use a Autofilter if you have many rows (faster)
See this page for more info
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim End...Copy and paste to the last row
Dear ALL, pls help me for macro.
I want copy the last row of column A, B and C.
and Paste the row down.
Example, my last row of A is A120, copy cell A120:C120.
then Past to A121
Thanks so much
if i understand correctly, this should work for you....
Dim r As Range
Set r = Range("A65000").End(xlUp)
r.Resize(1, 3).Copy Destination:= _
> Dear ALL, pls help me for macro.
> I want copy the last row of column A, B and C.
> and Paste the row down.
> Exam...Delete all rows except...
I receive a weekly report & have been asked to delete all rows except
for two particular rows. Here are the two criteria which are in column
AWH98228 and AWL99467
Can anyone offer help?
.ScreenUpdating = False
.Calculation = xlCalculationManual
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For r = LastRow To 2 Step -1 'Headings in row 1
If Range("A" & r).Value <> "AWH98228" _
And Range("A" & r).Value <> &quo...Hide Rows and Columns based on Date Input
Dates are listed in Column A, starting from Cell A3, and data appears
in the corresponding rows (across the page). Not every cell has data
in for the corresponding date.
For example: The date in cell A5 may have data in Cell B5, E5 & G5.
I would like to enter a date (using a command button and input box),
this will then hide all rows, except for the row which relates to the
date that has been entered. At the same time I would like it hide all
columns that do not have data in for the given date.
.... carrying on from example above.
If the user entered the date that appeared in cell A5...non scrolling angled row
I have seen this many times, but am unfamiliar with how to make it work
I wanting to angle a top row with headings at -45 degree angle. No
only the text, but the cells also. I would then like for that row no
to scroll. I only want the date below to scroll.
Any help is appreciate
Message posted from http://www.ExcelForum.com
You can't actually change the angle of cells. However, if you set the
text angle to -45 degrees and apply a border to the cells, the border
will also angle, so perhaps that's what you remember.
For freezing that row, see Freeze Panes in XL Help.
In art...Row numbers #2
I want to label and print row numbers for each row, however, I do not wish to
print column headings...can I disable the printing of the column headings?
Is there possibly a function to run in a cell to provide it a number or
You can only disable printing column *and* row headings.
<File> <PageSetup> <Sheet> tab,
Make sure "column & row headings" is *unchecked*.
Then, you can number your first column to mirror the row numbers, and that
will give the impression of displaying and printing the row headers.
You could e...Add a button to CRM Grid row
In MSCRM , Service Request details are displayed in the grid.
I need to place a delete button on each grid item and that need to be
enabled or disabled based on specific business rules.
How can I achieve this?
Do I need to extend the grid funcionality? or any other sugessions.
Reply to this message is well appriciated.
There's no supported or practical way to modify the grid functionality as you
want. C360 (www.c360.com) have a record editor that might work as a starting
point, but I'd be surprised if they let you injet your business rules.
So, that essenti...Row Count With Matching Cell Criteria
I need a formula that will count the number of rows (or times) in which
a cell has a date and the other has a certain tank number list. For
example the list in column A has tank numbers like the following:
In column D, most of the cells are blank until the tank is opened and a
date is entered.
I want to count the number of J tanks that have a date entered, the
number of V tanks that have a date entered, and etc. The closest I have
gotten is with a sumproduct formula but I can't figure out how to match
the certain tank letters with each date en...