Macros are useful for defining things that don't make sense "within" our programming language. For example, I write a lot of Scala code, and that language has a bunch of types like 'Tuple2[A, B]', 'Tuple3[A, B, C]', 'Tuple4[A, B, C, D]', etc. As a programmer we can see that these are all instances of some higher-level pattern 'TupleN[T1, T2, ..., TN]' but Scala has no idea about that; hence we can't write code which, for example, sums the first N elements of any tuple.
Actually, we can but it's not safe: Scala's tuples implement a 'Product' interface which we can use to loop through the values, but this loses a lot of type safety (looking up values via Product will upcast them to type 'Any'; our function's signature will ask for a 'Product' rather than specifically for tuples of numbers of at least a certain length, etc.)
Macros generate code at each of their call sites, which lets us automatically translate our 'higher level language' (e.g. 'Tuples of different lengths') into the 'real language' (e.g. Scala). For example, we can have a macro that translates 'tupleSum(5, 10, foo)' into a call like 'tupleSum5_10(foo)', where the function 'tupleSum5_10' is hard-coded to sum the first 5 elements of a Tuple10 (and is therefore acceptable to Scala, since it doesn't try to abstract over tuple lengths), e.g.
def tupleSum5_10[T1, T2, T3, T4, T5](t: Tuple10[Int, Int, Int, Int, Int, T1, T2, T3, T4, T5]): Int =
t match {
case (x1, x2, x3, x4, x5, _, _, _, _, _) => x1 + x2 + x3 + x4 + x5
}
Of course, we don't want to be writing functions like 'tupleSum5_10' manually. Hence we can use
another macro to define these functions! For example, the above might be generated by a macro call like 'defTupleSum(5, 10)'. If we run this macro in a loop, we can define all of the sum functions for tuples up to, say, Tuple20.
If we provide this in a library, users can treat tuples in a more high-level way. Since each macro call just generates some boilerplate Scala code, the result will be type-checked, etc. to make sure we've not done anything dodgy, e.g. since a call like 'tupleSum(5, 10, foo)' expands to 'tupleSum5_10(foo)', Scala will check whether 'foo' matches the type 'Tuple10[Int, Int, Int, Int, Int, T1, T2, T3, T4, T5]'.
I think the most famous use of macros in Scala is Shapeless https://github.com/milessabin/shapeless/wiki/Feature-overvie... (although I've not personally used it)
Note that the exact same problem with tuples happens in Haskell too, which can also be worked around using Haskell's macro system (TemplateHaskell).