My main issue here is that this is basically a punishment in the lore for any character whose player doesn't want to bother wading through the massive list of deities and picking one that they like. No matter how noble their deeds, no matter how many lives they save, no matter how much good they do, no matter how many evil plots they stop, a character can't get a good afterlife without checking a box on a form that says "Prayed to a god sometimes." The BEST a character can hope for is to be a guide, which basically means escorting others to paradises that they'll never actually be a part of.
I mean, the first thing I want to do when hearing about this wall is to have an adventure all about destroying it and slapping the collective pantheon in its face for thinking this was a good idea. Even supposedly "good" deities like Mystara and Ilmater are totally on board with punishing everyone who doesn't massage their egos on a regular basis.
Discovering that the faithless are being put into a wall and setting out on a perilous journey to convince Kelemvor to change is exactly the kind of thing high level adventures might want to do. Especially those who are concerned about their fate in the afterlife.
Respectfully, I think this point of view is a failure of imagination.
Moral propositions in the forgotten realms-verse come from the gods, and the gods exist because they have worshipers (since the time of troubles anyway). The reason why the paradise for people who worship Torm is peaceful, orderly, and perfectly beautiful, whereas the paradise for people who worship Odin is a realm of elemental chaos and violent personal heroism, is that those places are physical manifestations of the principles to which those gods and their worshipers assign moral value.
The strange thing in my mind is that any people in the forgotten realms would actually end up in the wall of the faithless.
Faith is a weird word to use for it. The gods clearly exist and, not only do they exist, they actually respond to intercessory prayer. Think about it, if you believed in the moral worth of violent personal heroism, why the heck wouldn't you worship Odin? I think a forgotten realms character who learned about the wall of the faithless would be puzzled, not morally indignant. After all, how could someone fail to worship a god? It's easy, it's potentially rewarding, and it has no appreciable cost.
In any case, if the principal concern is a PCs religious convictions, I'd suggest you simply handwave it, i.e. "my PC prays to a minor god that represents his/her beliefs," and ignore it for the rest of the game.
I'm not sure atheism is the right word when dealing with the forgotten realms. I mean it implies a disbelief in gods, which in that 'verse probably has more to do with trauma, mental illness, or extreme indoctrination than anything resembling a personal decision.
Anti-theists? As in opposed to gods in general?
Exactly right. Atheism follows from logical arguments based on observations of our reality. In a different reality with different observations, like gods that regularly murder people with lightning bolts, Atheism becomes a rather silly point of view.
A character being against the whole system of gods and outer planes strikes me as weird as well. Consider: If given a choice between knowing that your prayers would land you in your preferred afterlife--and that everyone else gets the same deal--or having no idea what happens to you after you die (potentially you just cease to exist), why would you prefer the latter? Would you advocate the latter to the extent that you risked being killed with lightning bolts and having your soul imprisoned in a torture wall for all eternity?