You should have a look at Kernel, vau-calculus and F-expressions. Kernel merges the notions of first-order functions and macros by having lexically scoped definition of Fexprs which takes their argument by name and also receives the call-site environment so that you can eval the argument in it if needed.
I find it a very elegant way of having everything clean, "lambda" does not even have to be a primitive anymore.
Look at the uses for Template Haskell, for example.
That's basically Haskell's macro system. And it's used quite a lot, though it's kind of arcane.
If you want to create an abstraction that defines one or several data types, you'll think hard about whether you can use some kind of type-level programming instead—but if that's not possible, or not convenient, you can use TH macros.
For example, the `lens` package defines TH macros for creating special kinds of accessors that are tedious to write by hand.
Which things cannot be expressed without macros? Macros run at compile time, and just output normal code in the language.
I think being able to generate types is a very useful and important use of macros. In fact, (depending on whether or not your macros can have side effects) you could use macros to implement something like F#'s type providers.