Beginner's guide to band structure follows. I've taken considerable liberties with the details to simplify this so don't take it too literally!
This is going to seem an odd place to start, but consider filling up the atomic orbitals in an atom with electrons. If you take a noble gas, e.g. Xenon, you'll find each orbital is filled completely with two electrons and this is why Xenon is inert. If you take Potassium instead you find all the lower orbitals are filled with two electrons, but the outmost orbital contains only one electron so the orbital is only half full. This is why Potassium is very reactive.
When you clump atoms together into a solid, the interactions between the atoms spread the sharp atomic orbitals into energy bands. Suppose our solid contains $n$ Xenon atoms, then each band can contain 2$n$ electrons. But each Xenon atom contributes 2 electrons to each band, so the energy bands in solid Xenon are all full. That's why solid Xenon is an insulator. In the case of Potassium all the lower energy bands are full up with 2$n$ electrons, but the top band contains only $n$ electrons, i.e. it is only half full, because each Potassium atom has only 1 electron left over to put into this band. That's why solid Potassium is a conductor.
The position of an electron in an energy band doesn't just determine its energy, it also determines its momentum. If you want to make an electron move so it can conduct electricity you need to change it's momentum, and therefore you need to change its position in the energy band. But when bands are full you cannot change an electron's energy/momentum because there are no free spaces in the band for the electron to move into. That's why filled bands are insulating and part filled bands are conducting.
Now, if you imagine taking your solid and filling up the energy bands with electrons there is going to be a highest occupied band and a lowest unoccupied band. Now the nomenclature can be a little confusing. If the highest occupied band is full (like solid Xenon) we tend to refer to it as the valence band, and the lowest unoccupied band as the conduction band. The energy difference between the bands is the band gap. The reason why we call the lowest unoccupied band the conduction band is because any electrons that get excited into it will conduct; electrons in the valence band won't conduct (because the valence band is full).
But, if the highest occupied band is only part full (like solid Potassium) we call this band the conduction band because the electrons in it can conduct. Strictly speaking the highest band is both the valence band and conduction band, but convention dictates we call it the conduction band. In metals we're usually not fussed about the lowest unoccupied band and the band gap because they aren't involved in conduction of electricity.
Now, on to transparency. When a photon interacts with an electron it transfers it's momentum to the electron i.e. it changes the momentum of the electron. But if you recall from above, you can't change the momentum of an electron in a full band. The only way to change the electron momentum is to hit it hard enough, i.e. with enough energy, to make it jump over the band gap into the lowest unoccupied energy band. So if you measure the optical absorption as a function of energy you find there's little absorption until the photon energy matches the band gap, and the absorption suddenly rises. For many materials the band gap energy corresponds to ultra-violet light, so the solid doesn't absorb visible light i.e. it's transparent. As you say, these solids are also insulators because the same mechanism (change of electron momentum) determines both conductivity and optical absorption.
In metals the lowest occupied band (the conduction band) is only partially full so electron momentum can be changed by any amount you want. That's why metals absorb light (and radio waves etc) very strongly and are opaque.
Incidentally you do get borderline cases. Pure silicon is an insulator, but the band gap is only about 1.12 eV and this is less than the wavelength of red light. So silicon absorbs light even though it's an insulator. Well, it's an insulator in the dark. As soon as you shine light on it the electrons you excite over the band gap conduct electricity, so silicon conducts when you shine light on it.
I hope all this helps. If you want to clarify any of the above please comment.
You are thinking in terms of atoms and molecules and you are mainly talking of solid state matter .
Solid state is another quantum mechanical phase, it has lattice structure with much smaller energies than atomic and molecular transition structures. Lattices have vibrational levels which are mainly responsible for the black body radiation solids emit, infrared is also photons.
A rule of thumb with radiation impinging on solids is that if the wavelength is smaller than the lattice dimensions the photons can penetrate easily the lattice, interacting only with direct scatters hence the higher penetration of X rays and gamma rays. Here is an article that discusses the penetration of radiation, X rays and higher.
For glass and optical frequencies there is a good answer here in this site., essentially the structure of the transparent materials is such that the photons pass through without loosing energy in the visible.
For infrared where the wavelengths are large in comparison with lattices or distances between molecules in liquids, the photon can give up its energy in collective excitations at the surface gradually heating up the material.
For ultraviolet, glass, depending on the type, has some absorptive bands, the photon energy transferred at the surface to collective modes or breaking molecular bonds and transformed to heat ( infrared) further in.
So your
Once you reach a critical frequency, however, the photons will begin to be absorbed because they have enough energy to excite the electrons (which is why glass is opaque in ultra-violet).
has small probability to happen until x-ray energies are reached which are the energies of bound electrons, and the link above gives the dependence in a simplified manner.
Best Answer
First, I want to dispel the notion that there is a single general rule or pattern. Indeed, the diversity of interactions between matter and radiation allow for many interesting physical phenomena (and human inventions!) So, in short, the rules of radiation are more like a long list of exceptions.
There are many different mechanisms for absorbing or scattering light which are relevant in different situations. I provide some examples:
Rayleigh scattering, when the wavelength of light is longer than the particle size, but is stronger for shorter wavelengths (why the sky is blue).
Mie scattering, when the wavelength of light is shorter than the particle size, where an atom just acts like a solid ball.
Resonant absorption, when the photon has the right amount of energy to interact resonantly with a molecule or atom (this is where absorption lines/bands come from).
Photoionization, when the photon has enough energy to liberate an electron.
Compton Scattering, when the photon has so much energy that the atom holding the electron doesn't matter much, it just collides with the electron.
These mechanisms all have different cross sections and regimes where they operate. This makes it very difficult to pinpoint a single pattern or cause.
Visible light in the atmosphere experiences Rayleigh scattering, since a few hundred nanometers is larger than air molecules. Since air has very few molecular transitions in the visible light range, visible light passes through easily. The same holds for glass, which blocks IR but not visible.
Ultraviolet is easily absorbed by atoms with ultraviolet photoionization energies or excitation energies in the atmosphere, like ozone. It turns out that ~1-10 eV is the energy range of chemistry (and the energy scale of an electron in a Hydrogen atom, for example) and many materials interact strongly with UV in particular. But there are exceptions, like blacklight filters, that will block visible light but not UV.
Increasing to X-ray and gamma ray energies, we leave behind the energy range of chemistry, and now photons are primarily bouncing off of electrons as though the electrons are billiard balls. Material properties matter less and less. So lead, concrete, and water are all fine candidates for blocking gamma rays.