In thinking about this question, we must first realize that we do not have any scientific knowledge of what American opinions were on the subject of Prohibition. Today, we have scientific polling that gives us a fairly clear and accurate understanding of public opinion on various issues. In the 1920s, there were no such polls. This means we do not know for sure what people thought.
For the most part, however, it is clear that most people lost their enthusiasm for Prohibition as the 1920s moved along. There were people who continued to believe in Prohibition. These were people who were true believers in temperance. For many other Americans, however, Prohibition was losing its allure. It was clear that Prohibition was not working and that it was helping organized crime to prosper. This turned many Americans off on the experiment. In addition, many people were coming to see Prohibition as a joke. They did not think that it was wrong to drink alcohol themselves. Therefore, they felt that Prohibition was not something that should be taken seriously.
Thus, by the end of the 1920s, most Americans either felt Prohibition was pointless or they felt that it was actively hurting the country. For these reasons, it was very easy for the 21st Amendment to pass. It was ratified in December of 1933, less than a year after it was proposed by Congress.