list data without blank rows
I have a spreadsheet full of data. I want to organize my data on a second tab
by looking in a single column and if a text value is present (such as an "M"
or a "K"), then copy data in the same row to the second tab. But not every
row will have a text value, some may be blank and I don't want a bunch of
blank rows on my second tab sheet. Any ideas??
One way .. a simple, dynamic formulas option
Assume source data is in Sheet1, cols A to C, data from row2 down
Assume the key col is col B (ie your "single" col to check whether there is
a text value present)...Tab Order when Word Table Rows are hidden
I have created a Word Form with 4 tables in which based upon certain
values selected rows are hidden. In my testing I noticed that when I
tab from one field to another that the tabbing appears to be going
through ALL of the hidden form fields before finally arriving at the
unhidden form field.
Is there any way to control the tabbing when you have rows hidden in a
Thanks in advance.
If you are going to use Tab, no. It will tab through the formfields,
regardless of whether they are hidden text, or not.
>I have created a Word Form with 4 tables in which...Formatting a Row Question
Is it possible to conditionally format an entire row based
on input into one cell in that row, or am I "rowing"
All help on this appreciated,
Yes, perhaps a simple example to illustrate ..
Select row1 (click on the row header)
Click Format >Conditional Formatting
Under Condition 1, make the settings:
Formula Is | =$B1=1
Click Format button > Patterns tab > gray > OK
Click OK at the main dialog
Now key in a "1" into B1, press Enter
Row1 will be coloured gray
Clear B1, the colour disappears
(Note that the $ sign in the formula is import...Why does my delete routine cause 2 rows to be removed from my DataGridView?
My code (below) causes 2 rows disappear from the DataGridView when I
expect it to delete 1.
I'm going around circles...can someone please steer me in the right
In the following code,
> clicking the Yes button makes 2 rows disappear from the DataGridView, (which I don't want) but only 1 delete from the underlying db (as desired).
> clicking the No button makes 1 row disappear from the DataGridView, (which I don't want) with none deleting from the underlying db (as desired).
> clicking the Cancel button has the same effect as clicking...Unhide query
I hid query (changed attributes to hidden) and I would like to see it back
and I don't know how. Can you help?
Access 2003 or earlier, with the database window active
== Select Tools: Options
== Go to the VIEW tab and check Hidden Objects
== Click OK
== Go to the Queries tab
== Find your query and click on it and select properties
== uncheck Hidden and click ok
== Go back to Tools: Options and uncheck Hidden Objects
In 2007, you will need to start with the Office button to get to the option so
show hidden objects.
Access M...Copy Range but Skip blank rows Q Options
How do I copy (values) a range of cells that have blank rows in
between to another range without the blank rows. I thought it was
paste special values-skip blanks, but when I do I still see the blank
eg Copy values in A1:A5 (blank rows are in A2 & A4) to M1:M3
The Skip Blanks option on Paste Special relates to the destination
range - it allows you to avoid pasting over data which is already
there with blank values in the source range.
To do what you want, apply autofilter to the source range and select
Non-blanks from the filter pull-down - then when you copy this, ...how do i unhide all cells on an excel worksheet?
I selected my entire worksheet (by clicking the button in the upper-left
corner of the worksheet frame), then I right-clicked and choose Hide. I've
tried every trick I know to unhide the cells but nothing works. Does anyone
have a suggestion?
Select Format | Row | Unhide
"alliecallie" <email@example.com> wrote in message
>I selected my entire worksheet (by clicking the button in the upper-left
> corner of the worksheet frame), then I right-clicked and choose H...Hiding and Unhiding Rows.. Unhide does not work..
Operating System: Mac OS X 10.5 (Leopard)
I use this formating feature quite often. But, on this one worksheet which is a rather large data list (6000 Rows) I can NOT unhide rows that I have previously hidden. It just does not work. <br><br>I have other workbooks open and I have no problem hiding and unhiding in the normal way. <br><br>The workbook is NOT protected. The cells are not locked. But the file was originally a windows file. <br><br>Is there some switch I do not know about?
This is possibly a rare occurren...Comparing and Deleting rows
I have two spreadsheets.
One table has these fields:
Computer | Date
The other is like this:
Computer | Last Used
The problem is that the two lists are different, but some computer
exist in both. The first list is a complete list, the second one is
subset of the first.
How do I extract rows that are common into a separate table
Message posted from http://www.ExcelForum.com
> I have two spreadsheets.
> One table has these fields:
> Computer | Date
When I try to edit a macro I get the following error message:
Cannot edit a macro on a hidden workbook. Unhide the workbook using the
How do I get rid of this error?
When I right click on the sheet, it's not hidden.
...When any cell in row 19 (eg K19) equals X I want the cell in row 25 3 cells before K19 (i.e. H25) to eqaul Y.
Any tips will be greatly appreciated.
Re: When any cell in row 19 (eg K19) equals X
I want the cell in row 25 3 cells before K19 (i.e. H25) to equal Y.
One way to get it done ..
Copy right across to IV25
Above of course, assumes that there's only a single "X" within row 19 at any
time. If you got more "X"'s in row 19, it'll return only the result for the
1st one ...Removing duplicate cells/rows in excel
I have a spreadsheet with 1,000 rows.
Each row has a persons name (e.g. James J Smith) as the
There are multiple entries for a single person (e.g. James
J Smith). The first entry found is the one with the latest
I need to remove all the duplicate rows for a single person
so that I end up with one entry per person that is the
first one found in the spreadsheet.
What is the best way of doing this?
A macro would probably be the most efficient way, though would could do it
manually using an oldest>newest sort and conditional formatting to highlight
duplicates and...Showing Duplicate Rows
I have seen the question with that same title however that is not exactly
"showing" duplicates. It is more showing unique (not duplicate) I would
like to truly show the duplicates.
For instance there are several duplicate product numbers that are in my
sheet and I want to display all the duplicates so that I can make corrections.
How can I filter so that excel will only show those rows that have duplicate
Use data>filter>autofilter>filter by column...Sum a table of columns & rows
I have a spreadsheet of 154 Rows (all unique project numbers in numerical
order) and 9 columns of account numbers (some are similiar and some are user
entered, therefore there could be 'blanks' with no data in them).
I am trying to create a table that will only give me the project number if
there are dollars in one or more of the columns. This would be used for data
entry (and that is why I would like to have the columns summed up - to remove
Any ideas? I have given a brief example below:
F, G, &am...Comparing 2 coulumns in different worksheets and printing the rows that match
Sorry to bother everyone. I am trying to learn some Visual Basic t
maybe make a function that woudl do what i need, but that is goin
slow. What i am trying to do follows.
I have created a perl script that uses the microsft progra
hfnetchk.exe to find what patches are needed on all of our systems her
at work. it gives me a text print out that is tab delimited and
import that into excel on the *Srvneed* sheet. *Column B* in this shee
has the patch numbers for what is needed.
Now our IT security also has a lis...unhide fails everytime
I have a macro that hides all columns and rows with no data in them.
To re-edit the thing and fill in the data, I have to unhide those rows and
I've got the unhide help screen staring me in the face.
if I GOTO a1 and choose unhide from the format menu NADA
if I select b2-> right click and unhide NADA
select B1 format->unhide NADA..
What am I missing ?
more pix @ http://members.toast.net/cbminfo/index.html
First, I don't see an Unhide option under the Format dropdown.
Are you looking at Format|sheet|unhide, format|column|Unhide,
...Add row between current rows in a way to long sheet....
I need to add 1 row between each current row in an 2670 rows long excelsheet.
And yes I am lazy, so could anyone help me create the macro? Seems I'm doing
something wrong... :(
PS: I'm working excel 2003.
Thank you for your help!
Just fill a simple sequence 1, 2, 3, ... 2670 in a blank column next
to your data (imagine this is column H). Then copy that sequence down
to H2671 and down. Then highlight the block of data (including the
helper column) down to row 5340 and perform your sort, using column H
as the sort key. You can delete column H afterwards.
Hope this helps.
On...Mark Rows for scrolling
This seems weird!
I mark row 4 and row 1 and click on Freeze Panes.
Rows 1-3 become frozen.
There's also a black line below Row 6 that acts like another Freeze (Rows
Does anybody have an answer to this?
I'm not sure what mark means here, but freeze panes will freeze rows above the
activecell and to the left of the activecell.
So if you meant that you selected rows 1:4, then it would only matter what cell
was the activecell. If that activecell is in row 4, then rows 1:3 would be
(You can select a multicell range and tab through th...Insert Row Under current row (with formulas/formatting)
I've got a sheet that gets appended to regularly but have to do a lot
of fiddling to make sure the formatting is correct, calculated fields
get added and chart series ranges collect all the data every time I add
a new row.
The insert (row) command seems to take care of all of this but it
inserts the empty row above the current row. As I'm always appending
data to the sheet, I would prefer this to be below the current row.
I've tried adding a dummy row under all the data containing the
formulas and formatting I need but unfortunately I get formula errors
and it screws up my chart....Automatically Break Multiple Rows to Two Columns
I have a long list of rows in Excel, consisting of 2 columns (Column A:
Item & Column B: Quantity), as shown in the example below:
I want to automatically break the long list of rows at every Nth row
into another 2 columns (Column E & F). Example as shown below:
A B E F
Item Qty Item Qty
AAA 12 FFF 230
BBB 130 GGG 3...Repeat Values in rows
I need to search my worksheet (128 X 128) rows for any repeated values,
It doesn't sound like a charrting question, but in your worksheet (having
selected your 128*128 range & with cell A1 active), use Format/ Conditional
Formatting/ Formula Is:
and select a format to mark your duplicates.
"Governor" <Governor@discussions.microsoft.com> wrote in message
>I need to search my worksheet (128 X 128) rows for any repeated values,
> anyone ...email by rows
I'm looking for a macro that will go down a sheet and look for th
email address and mail every thing in that row to *ONLY* that person n
matter if it has 10 columns or 20 columns I have a sheet set up lik
Employee Name LAB Degree Section E-Mail Address paper1 paper2
I will be adding more columns as the year goes on that is why it nee
to be able to send different size rows.
Any help would be appreciated
Message posted from http://www.ExcelForum.com
Which mail program do you use
Regards Ron de Bruin
"D...Insert Row by a defined Number
I have a macro that inserts a line, how can i modify it to insert a line
by a number in a cell corresponding to the row number?
Inserts a new row between rows 22 and 23
Inserts a new row between rows 10 and 11
comotoman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27292
View this thread: http://www.excelforum.com/showthread.php?threadid=475101
> I have a macro that inserts a line, how can i modify it to in...How do I number each row in a query result?
If there are 10 rows in the select query result, how can I have the query
number each row from 1 to 10?
Thanks in advance.
What will having "row numbers" allow you to do?
Microsoft Office/Access MVP
"srp822" <firstname.lastname@example.org> wrote in message
> If there are 10 rows in the select query result, how can I have the query
> number each row from 1 to 10?
> Thanks in advance.
I want to have the results numbered. It's not really about having row
I would like a macro to review rows 7-435 and if Col F = zero, then hide
that row so that only rows that contain >0 show at the completion of the
I can't add an auto-filter to help acheive this because I already have an
auto-filter on a section that is located beneath row 435.
Dim RngCol As Range
Dim i As Range
Set RngCol = Range("F7:F435")
For Each i In RngCol
If i.Value = 0 Then _
i.EntireRow.Hidden = True
Gord Dibben MS Excel MVP