deleting blank rows for up to 60000 rows of data
I have worksheets with up to 60000 rows in one column I have tried using the
following macro from this site and it does not do anything. Any ideas?:
Dim i As Long
.Calculation = xlCalculationManual
.ScreenUpdating = False
Lastrow = ActiveSheet.UsedRange.Rows.Count
For i = Lastrow To 1 Step -1
If WorksheetFunction.CountA(Rows(i)) = 0 Then
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
The code works f...Referenced constants
If i have two projects (A and B), will the use of a constant in A that was created in B cause a "get" method to be
created to get the constant or will have just have the value? I think the latter because i compiled the below into
different assemblies and then decompiled them with .netreflector.
The below is the IL. i am not good at reading IL but i don't see a method call in here anywhere. I guess this brings
up the point. If i change B's constant but don't recompile A, how does a get the new value? Maybe i am missing
..class public auto an...Filter a Report
I have a report that has two fields called "ManifestNumber" (number field)
and "RecievedOn" (Date/Time).
Then I have another form with two buttons. "Print New Manifest" and "Reprint
Both of these buttons opens my report, but I like the first button, "Print
New Manifest", to filter the report for "RecievedOn" field that contains
The second button asks to enter a Manifest Number. Then it should match this
number with the "ManifestNumber" field of the report and filter it for that
...Hiding "0" values in pivot table
In a worksheet I have a listing of stock records with ItemNumber, Date,
StockLocation and NumberOnStock. When I make simple Pivot Table with
ItemNumber and StockLocation as rows and select a Sum of "NumberOnStock"
I would like to hide rows where the sum is "0". But this does not seem
to be possible.
Tried to follow the guidelines in the Microsoft article:
But no luck :-(
You could use conditional formatting on these cells - highlight the
cells,...Hiding formuals in cells question
Hi. I was wondering if there was a way to hide the formulas in cells without
protecting the sheet. I am making something for other teachers at my school
and I want to hide the formulas so they won't mess anything up on the
spreadsheet. Any help would be greatly appreciated. Thanks!
not without protecting the sheet but why is this a problem for you?
"JP" <JP@discussions.microsoft.com> schrieb im Newsbeitrag
> Hi. I was wondering if there was a way to hide the fo...Automatically Highlight Every Second Row in Excel
I would like to know if there is any way to have excel automatically
highlight every second row in a sheet to make it easier to read accross the
Can you use conditional formatting?
If yes, see Chip Pearson's site:
> I would like to know if there is any way to have excel automatically
> highlight every second row in a sheet to make it easier to read accross the
...hide my name
When replying to an email or creating one from scratch how do i hide my name
in the "from" field...thx
fred <email@example.com> wrote:
> When replying to an email or creating one from scratch how do i hide
> my name in the "from" field...thx
Are you saying you want the reply to be anonymous?
Brian Tillman [MVP-Outlook]
no, I don't mind if my email is revealed.. just my name. I have tried by
removing it from the "User Name:" field in the email account but it
continues to show up. I have also removed from the header informati...Hiding and Un-Hiding Data fields
I am using a form for people to select data via dropdown menus and then
having it run through a query to filter results. Is there a way that I can
have certain feilds hidden until a toggle button or something of the like is
Basically I want have three choices of buttons: Year, Quarter, and Month.
What I ideally want to happen is when someone selects the button for year, a
hidden drop down menu appears where they can then choose from the available
years to filter through the query.
I would say I am above average with using access, but I have no idea how to
hide fields and then...How to get rows from 4 diff permitations??
Wondered if you could help.
I have 2 tables of data like so:
TID PID YEAR TERMID MATHSLEVEL READLEVEL
1 1 2006 0 W1 W2
2 2 2006 0 1B W4
3 3 2006 0 W1 W2
4 4 2006 0 2C 5A
5 1 2007 1 W1 W2
6 2 2007 ...How sort filtered file.
I m facing new problem in these days. I have workbook which contains data
s.no. Name of Customer BillNo. Date Concession Form Form No. Amount
How can i sort data after filtering by customer name in ascending order with
serial no. I want to sort all data by ascending order of customer name.
Rao ratan Singh
As far as I know, if you sort it after filtering, you'll only sort the
filtered data, not the whole data set. If you want the whole data set
sorted, you'll need to do that before filtering.
"Rao Ratan Singh" <RaoRatanSing...How do I hide gaps in an Excel 2007 chart?
I have a bar chart that is showing gaps and i don't want them. The select
data > hidden and empty cell button does not give an option to hide gaps. I
think by default they are hidden but if you even click on this to see what it
does the gaps are shown and no way to turn off.
If you hide the actual rows then with the Plot visible cells only
setting on the gaps will be removed.
In xl2007 the setting is on the Select Data dialog, Hidden/empty cells
> I have a bar chart that is showing gaps and i don't want them. The s...Command messages for FrameWindow member objects
i am working on an SDI application and in that application i need to
broadcast a message to all the objects. So i used:
SendMessage(WM_COMMAND, ID_SOMEID); // From within the document Object
Then i added ON_COMMAND_EX macros in various objects for handling and
further passing the message. I realized that MainFrame object was
getting the message but a dialog bar object which is a member of
MainFrame was not getting this message. I tried a lot but couldnt
figure out why dialog bar object is not getting my command message when
it is getting other command messages and update UI comma...Hiding a worksheet #3
I want to hide a worksheet. So, I went to the format menu and the "worksheet" option is grayed out. Why? And, how do I "ungray" it.
is your worksheet protected ?
> I want to hide a worksheet. So, I went to the format menu and the
> "worksheet" option is grayed out. Why? And, how do I "ungray" it.
I think Frank meant to ask about the Workbook--not the worksheet.
(Check under Tools|protection|Protect workbook or Unprotect workbook)
My question is: Is there another sheet in the w...html filter
Office 2003 can not use the HTML FILTER for Office 2003.
Also...WORD has a Save to Filtered HTML Web Page setting which lets you save
to HTML without the "extraneous" Office html code which is used for
roundtripping to HTML.
Unfortunately, Excel doesn't seem to have the same "Save to Filtered HTML"
setting. Is there a workaround?
...Hide images when Hiding Rows
I have inserted images into cells, but when I hide the column the images
are not hidden with that column, they just get squished between the two
cells next to the hidden cell. Is there a setting or way to tie the
image to that cell so that it hides/unhides appropriately with the row?
kurt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35686
View this thread: http://www.excelforum.com/showthread.php?threadid=554712
You cannot insert an image into a cell.
You can overlay ...setting filter rule for email
Hi, can someone tell me how to set up a rule to
automatically put incoming email with a missing name in
the "From" field (i.e. the sender field is blank) in the
trash folder? Thanks, ~C
...Need to display an error message when a duplicate object is clicked.
Hi all, I'm new to Access and I'm creating a simple IT Inventory
database. My first form just has details on the device, and I want to
be able to have the users select an IP address from a dropdown list.
I have the list and the dropdown working flawlessly, but in my table I
selected No Duplicates, so that no one IP address can be used twice.
What I'd like to do, is that if the DB detects a duplicate, to pop up
a dialog box stating that the IP address is already in use and to
please select a valid IP address. By default, there is an error that
pops up when you try to save the rec...Hiding plot Area
I have a formatted output sheet that has a Pie Chart
embedded. The data for the pie chart comes from a second
sheet using 2 ranges B2:B6 & D2:D6 B2:B6 is always
populated and contains labels. What I want to do is, when
D2:D6 contains 0 in each cell (or Null), suppress the Pie
chart but always show the Legend (which is driven by B2:B6)
All you see of the pie when all values are zero is a single line at the
first slice. The legend still appears. To hide the line, double click on
one of the wedges and on the Patterns tab, change the Border setting to
None. ...how to hide an ActiveX control?
i have an ActiveX control, written in C++, which has the
OLEMISC_INVISIBLEATRUNTIME flag set, yet when i display the web page
it is attached to, i get the familiar "red cross" graphic in the
middle of the screen - how can i hide the control so nothing is
displayed at all?
can u set the size of ur control as 1x1 pixels
"bhu Boue vidya" <firstname.lastname@example.org> wrote in message
> hi there
> i have an ActiveX control, written in C++, which has the
> OLEMISC_INVISIBLEATR...XslTransform object, Transform method
Of all the eight overloaded Transform methods in XslTransform, none of
them write to a string. (They write to streams, readers, files, etc.)
Anyone know how to get this to a string?
xslDoc.Transform(xmlDoc, Nothing, ???)
How can I get the transformed result into a string?
Jonathan Wolfson wrote:
> Of all the eight overloaded Transform methods in XslTransform, none of
> them write to a string. (They write to streams, readers, files, etc.)
> Anyone know how to get this to a string?
&...Should Globally Needed Objects be Members in Main Frame, Application, or be Global?
I have been making objects needed by my entire application members either
the Main Frame ( main window derived from CMDIFrameWnd ) or the Application
(derived from CWinApp ). To get to these members I use either GetMainWnd()
or AfxGetApp() to get pointers I can access them with. Now I wonder if there
is any reason not to make these objects global so the use of GetMainWnd() or
AfxGetApp() can be dispensed with.
What are the the relative merits of putting objects needed globally for the
duration of program execution in these three places?
I typically create a singleton or static class...Hiding rows with VBA
In my sheet called "insertsheet I have this VBA:
(thanks to some users of Google groups)
Private Sub Worksheet_Change(ByVal Target As Range)
Const nMAX As Long = 10
Dim nRows As Long
Application.ScreenUpdating = False
If Intersect(Target(1), .Cells) Is Nothing Then Exit
nRows = .Value
.Range(.Cells(1, 5), .Cells(1, _
.Columns.Count)).EntireColumn.Hidden = True
.Range(Cells(1, 5), .Cells(1, _
I have data in cells A7:DK394 which all have 0 in them until totals are added
in other worksheets which then pullthrough.
At the end of the week I have to Hide all rows from A7 to A394 that still
have 0 in them as no totals have been added in other sheets so the customer
has not posted with us.
is there any way excell can recognise the cells that have no data against
them and hide them automatically by pressing a button. or if they all start
off hidden unhide as data is entered.
Hopefully this makes sense
many thanks for any help with this as again its to save me lots of...object variable or with block variable not set
I have a problem, once i am trying to open the acess i am geting this
message Data type mismatch in criteria expression when i press ok i
get object variable or with block variable not set and i cannot access
What can i do
On Mon, 7 Dec 2009 01:00:13 -0800 (PST), Tia
Hold down the Shift key while you open the database, so the AutoExec
macro does not run.
Also, when you get an error you can hit Ctrl+Break to break into the
code and inspect it.
Microsoft Access MVP
>I have ...How do I hide email addresses in outgoing mail
Hint: You post your question in the body of the post, not the Subject.
Hint 2: Use the BCC field.
"Dennis" <Dennis@discussions.microsoft.com> wrote in message
"Dennis" <Dennis@discussions.microsoft.com> wrote in message
PLEASE write your question in the body of the post and NOT just the subject
Have a look here: http://dts-l.net/goodpost.htm
As posted here MANY MANY times - put them in the BCC...