The normal process is:
CPU attempts to fetch an op-code instruction byte from the video file. ULA steals that byte as a character code and feeds the CPU a NOP op-code byte instead. Immediately following that is a refresh cycle. The ULA uses the top part of the refresh address but substitutes the character code just read and the contents of an internal three-bit counter at the bottom to form a ROM address. Hence, a row of a character pattern is returned. It ends up in a shift register within the ULA and becomes video.
All RAM is static, so this misuse of the refresh cycle isn't problematic.
The three-bit counter can be reset by software, as can the top part of the refresh address — the Z80 uses the I register for it. For Manic Miner and some other titles, the trick is simply to point to an existing region of the ROM that has close to the full 64 range of possible characters sufficiently close to each other, reset the three-bit counter every line and use a mapping table. You'll notice it's not exactly perfectly mapped; weird distortions occur. That's partly because of the mapping from 256 input values to 64 character codes and partly because there's nowhere in the ROM that has all 64 possible byte sufficiently values near each other. So the game does its best.
No Limits and others use a separate observation: that the unaltered refresh address is observed on the expansion bus and by internal RAM. The version with the character code and row counter imposed reaches the ROM chips only. So they set the refresh address outside of the ROM area and inside the RAM area. Assuming RAM responds during a refresh cycle, what then happens is: CPU attempts to read dummy display byte, it's latched and forwarded, mixed with the row counter and refresh address, to the ROM which doesn't respond. RAM responds to the refresh address instead. That value goes into the ULA's shift register and is output.
Not all third-party RAM expansions respond to the refresh cycle but it's usually a simple modification and most do, anyway.
To follow along on a schematic, see e.g. the one redrawn by Ron Reuter available from here:
The ROM is IC2, has its low nine address pins (three for the row counter, six for the character code) connected both to address lines coming out of IC1, the ULA, and IC3, the Z80. Resistors R18–R26 prevent the ULA's address input from flowing back towards the Z80. As both the internal RAM and the expansion port are on the same side of those resistors as the Z80, they see the address the Z80 is generating only — they're unaffected by the ULA. It's almost a prototype of the ZX Spectrum's floating bus.
Chip selects are generated by the ULA; luckily they're independent of whether it knows it is expecting a character graphic. So when the generated refresh address is in RAM rather than ROM, the RAM chip select is active and the ROM isn't, and the ULA's mutation of the refresh address isn't visible to the RAM.
