The former England fly-half, 53, has resigned but will complete work on the union's latest agreement with Premiership Rugby before leaving.
"It feels like the right time to step down at the end of the season," said Andrew, who joined the RFU in 2006.
He will be replaced by former England scrum-half and captain Nigel Melville.
The RFU says Melville - chief executive of USA Rugby since 2006 - will be responsible for professional rugby in England with particular focus around Premiership Rugby, the English Qualified Player scheme, the elite player squad (EPS) scheme and the academy system.
Melville will also take over the responsibilities of former head of international player development Joe Lydon, who resigned in March.
RFU chief executive Ian Ritchie said it is an "exciting time" for English rugby.
"He will bring a great deal of expertise from not only his experiences working in England but internationally at USA Rugby," added Ritchie,
The former Wasps director of rugby - who won 13 caps for England and represented the British and Irish Lions - guided the Londoners to their first Premiership title of the professional era in 1996 before moving to Gloucester in 2002, winning the National Championship final and Powergen Cup.
Andrew added: "I hope and believe I will leave a secure foundation to build a successful future and I will continue to give my support in any way I can to achieve this.
"Having played with and known Nigel for a long time I believe he is the best candidate to be appointed to take over the reins from me and I wish him well and I will support him in any way I can going forward."
Rob Andrew will leave his role as the RFU's director of professional rugby at the end of this season, the Rugby Football Union has announced.