Windows Info

Replace NULL Excel cell contents only up to the last row in a dataset

by on Jun.02, 2021, under Computer Stuff, Windows Info

Recently, I needed to write a macro to replace all NULL values in a spreadsheet with a “0.00”. This seems easy, but if you write a default search and replace in your macro, Excel will dutifully replace *every* *single* NULL in your columns, even where there is no adjacent data. This means that your spreadsheet will grow to the maximum number of rows, because you added zeros to ALL NULL cells in the column. To only replace NULL values where there is adjacent data, you need to find the “LastRow” in your spreadsheet.

If you have to replace values in more than one column, you’ll need to rename the variable declarations as below in this example: (Note the Area, Area1, Area2, LastRow, LastRow1, LastRow2, etc. below)

## MACRO SNIP ##

Columns("D:D").Select
  Dim Area As Range, LastRow As Long
  On Error Resume Next
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
  SearchDirection:=xlPrevious, _
  LookIn:=xlFormulas).Row
  For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _
  SpecialCells(xlCellTypeBlanks).Areas
  Area.Value = "0.00"
  Next

Columns("E:E").Select
  Dim Area1 As Range, LastRow1 As Long
  On Error Resume Next
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
  SearchDirection:=xlPrevious, _
  LookIn:=xlFormulas).Row
  For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _
  SpecialCells(xlCellTypeBlanks).Areas
  Area.Value = "0.00"
  Next

Columns("F:F").Select
  Dim Area2 As Range, LastRow2 As Long
  On Error Resume Next
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
  SearchDirection:=xlPrevious, _
  LookIn:=xlFormulas).Row
  For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _
  SpecialCells(xlCellTypeBlanks).Areas
  Area.Value = "0.00"
  Next

This way, you get zeros just until the last row, and no more! Replace the zeros in the value of Area.Value = “0.00” with whatever you want put into the NULL cells!

Leave a Comment :, more...

Upgrade from LSI Logic SAS adapter to VMWare Paravirtual on existing VM

by on May.25, 2021, under Computer Stuff, General Info, Linux, Windows Info

By default, when you add a hard drive to a VMWare virtual machine, the SCSI adapter default choice is LSI Logic SAS. For speed and optimization, the VMWare Paravirtual driver is the better choice. However, you need to have the driver installed in the guest OS before just flipping your controller to a new type. If you just change the controller type to VMWare Paravirtual, and the driver is not installed in the guest OS, your VM may not boot.

  1. Edit settings on the VM, and add a new SCSI controller to slot 1, selecting “VMWare Paravirtual” as the type.
  2. Add a 1gb disk to the VM, adding it to the new slot 1 controller you created in step 1.
  3. Check compmgmt.msc on the VM to ensure that you see the new SCSI controller driver under “Storage Controllers”
  4. Optionally, update the driver on the guest OS by searching online for an updated version. I found one from 2019.
  5. Shut the machine down, and edit settings again. Remove the 1gb disk you created earlier.
  6. Click on the old SCSI controller in slot 0 and expand it. Change the type to “VMware Paravirtual”
  7. Remove the additional SCSI controller added in step 1.
  8. Click OK, and boot the server again.
  9. Once the server is booted, it should report that it found and installed a new driver, (again) and will request a reboot.
  10. Reboot the server.

Leave a Comment :, , more...

Office365 Content Search for External Recipients

by on May.17, 2021, under Computer Stuff, General Info, Windows Info

Recently, I was asked to find all emails sent to and from a particular user, from an external email address. The mailbox exists in Office365, so I hopped into Compliance Center, and started a new Content Search. In trying to fill out the form for the recipient, or participant, I realized that I could not choose or enter an external email address. When I try to enter an external SMTP address, it says that there is “No User Chosen”, and you cannot continue with creating the search.

To get around this, you can enter your search parameters in the “Keywords” box, and ignore the other fields, like so:

KEYWORDS:

(date=2019-07-30..2020-09-17)(senderauthor=localuser@localdomain.com)(senderauthor=remoteuser@externaldomain.com)(participants=localuser@localdomain.com)(participants=remoteuser@externaldomain.com)(participants=remoteuser2@otherexternaldomain.com)

Leave a Comment more...

Find and delete a file on ALL drives using powershell

by on May.05, 2021, under Computer Stuff, Windows Info

In the example below, replace filename.txt with the file you’d like deleted. It will be removed from all drives.

get-psdrive -PSProvider filesystem | ForEach-Object { Get-Childitem -Path $_.Root -Filter filename.txt -recurse | Remove-Item -force}

Leave a Comment :, more...

I was today years old when I learned…

by on Oct.15, 2020, under Computer Stuff, Networking, Windows Info

That you can do this:

dnscmd /createbuiltindirectorypartitions

Creates a DNS application directory partition. When DNS is installed, an application directory partition for the service is created at the forest and domain levels. Use this command to create DNS application directory partitions that were deleted or never created. With no parameter, this command creates a built-in DNS directory partition for the domain.

Leave a Comment :, more...

Recreating the Exchange 2013 Receive connectors

by on Feb.10, 2016, under Computer Stuff, Windows Info

Just in case you ever have to recreate the default receive connectors in Exchange 2013, here you go:

Default Client Front End Transport (FrontEnd Transport)
TLS, Basic, Integrated, Exchange users, port 587
Default Client Proxy (Hub Transport)
TLS, Basic, Offer Basic after TLS, Integrated, Exchange Server Auth, Exchange Servers, Exchange users, port 465
Default Front End Transport (FrontEnd Transport)
TLS, Basic, Offer Basic after TLS, Integrated, Exchange server auth, Exchange Servers, Legacy Exchange Servers, Anonymous Users, port 25
Default Hub Transport (hub transport)
TLS, Basic, Offer Basic after TLS, Integrated, Exchange Server Auth, Exchange Servers, Legacy, Exchange Users, port 2525
Default Outbound Proxy Frontend Transport (Frontend transport)
TLS, Enable domain security, Basic, Offer basic after TLS, integrated, Exchange server auth, exchange servers, anonymous, port 717

Comments Off on Recreating the Exchange 2013 Receive connectors more...

It’s finally here!! Defer Windows Updates using Group Policy!!

by on Nov.16, 2015, under Computer Stuff, Windows Info

Comments Off on It’s finally here!! Defer Windows Updates using Group Policy!! more...

Great post on User Certificate Autoenrollment

by on Oct.26, 2015, under Computer Stuff, Windows Info

If you’re setting up PKI, or 802.1x, go read this-

http://www.vkernel.ro/blog/set-up-automatic-certificate-enrollment-autoenroll

 

Comments Off on Great post on User Certificate Autoenrollment more...

R.I.P., 2003

by on Jul.16, 2015, under Windows Info

Microsoft ended support for Windows Server 2003 on July 14th, 2015

 

http://www.microsoft.com/en-us/server-cloud/products/windows-server-2003/

 

Comments Off on R.I.P., 2003 more...

CA Root services cannot start after CA Root certificate expires

by on Dec.04, 2014, under Computer Stuff, Windows Info

Since by design, you cannot recover from a CA root certificate expiring, sometimes you need to limp along, and continue to issue certs even though you cannot necessarily revoke them, because the CRL published in Active Directory is now incorrect, or offline.
While we can argue all day about the benefits/detractors of this, here it is:
To bring the CA Root back online after the Root certificate expires, issue these commands in an elevated powershell:

certutil –setreg ca\CRLFlags +CRLF_REVCHECK_IGNORE_OFFLINE
net stop certsvc && net start certsvc

Now, go back to the drawing board, and PLAN your PKI implementation, and DON’T LET YOUR CA ROOT CERTIFICATE EXPIRE!!!

Incidentally, once you’ve fixed your certificate snafu, to stop ignoring offline CRLs, do this in an elevated command prompt:

certutil –setreg ca\CRLFlags -CRLF_REVCHECK_IGNORE_OFFLINE
net stop certsvc && net start certsvc

Comments Off on CA Root services cannot start after CA Root certificate expires :, , , more...


Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

CryptedNets is proudly powered by

Entries (RSS) and Comments (RSS)
Register - Login

Visit our friends!

A few highly recommended friends...