What is happening at the end of the story when Madeline Usher appears?
"The Fall of the House of Usher" by Edgar Allan Poe is a horror story, in which an atmosphere of gloom, dread, and suspense leads up to a culminating moment of horror.
The nameless narrator is a boyhood friend of Roderick Usher who brings to the story a "normal" perspective of a sane person. He responds to a letter begging him to visit Roderick and travels to the ancient ancestral mansion where Roderick lives in an incestuous relationship with his twin sister Madeline.
Madeline suffers from a form of seizure disorder called catalepsy. An important fact to remember is that victims of this disease could enter into a state like a coma in which they appeared to be dead. Madeline, who has been gradually growing sicker, appears to die, and is buried by Roderick and the narrator. She did not actually die though, but had just fallen into a cataleptic fit.
When she awakens from the cataleptic state, she realizes that she has been buried alive and claws her way out of the tomb. Her brother Roderick hears noises that are due to this, but he isn't sure whether they are figments of his own imagination or real.
In the second to last paragraph of the story, Madeline finally breaks through to the main body of the house:
... there did stand the lofty and enshrouded figure of the lady Madeline of Usher. There was blood upon her white robes, and the evidence of some bitter struggle upon every portion of her emaciated frame ...
Madeline then collapses and her brother immediately dies from the shock of seeing her. The narrator in the final paragraph leaves the house and sees the whole house collapse behind him.
When Madeline appears, it is clear that she was buried alive; the reader does not know if this was done on purpose or whether Roderick thought she really was dead when he buried her. It could be that Roderick's mental state had deteriorated so badly that he tried to murder his sister by burying her alive. Perhaps he could not live with the fact that he had done some truly horrible things and he had to kill her to purge himself of them by doing so. Regardless, Roderick cannot handle the stress of seeing Madeline back from the dead, so to speak, so he drops dead of shock or fright or both!