Another benefit apart from macros is that you can replace whole subexpressions to modify the function code: https://github.com/raxod502/el-patch#el-patch Although arguably you can achieve the same with source code and other languages: https://github.com/adamchainz/patchy#patchy
Although (as a keen Emacs user & someone who often hacks into existing librairies) user I'm not really convinced either that homoiconicity plays that huge of a role in Emacs malleability in particular -- to me it's more about hooks/advice system and being able to dynamically override things to tweak into the way I want them to behave -- and I don't see a good reason why this couldn't be possible in other languages.
It is the first dynamic programming language, and arguably the most dynamic one. The only other non-esoteric language that comes close is PL/I, which also had macros and where every keyword could be redefined. But its standard is ungodly long, while Scheme dialects can be descriped in a few tens of pages. Also, it is quite easy to write an interpreter for core LISP: 100 to 200 lines, depending on your desire for readability.
Edit: Yes, this means there are lots of parentheses, which can be hard to read.
REBOL/Red are on a similar level.
I see no reason why a language can not have a more complex source -> AST transformation, while still keeping code as readable data that can be manipulated by macros. So, if you want homoiconicity with a slightly more complex syntax, I don't think it won't bring any problem.
But if you keep making your language's syntax more and more complex, you will make macro creation harder and harder, so there is probably a practical limitation somewhere. Operators and statement sequences are very likely not complex enough to be a problem, but I don't think anybody will ever make native C++ macros work.