Battle of Waterloo
They countercharged French cavalry numerous times (both brigades), halted a combined cavalry and infantry attack (Household Brigade only), were used to bolster the morale of those units in their vicinity at times of crisis, and filled gaps in the Anglo-allied line caused by high casualties in infantry formations (both brigades).
This service was rendered at a very high cost, as close combat with French cavalry, carbine fire, infantry musketry, and—more deadly than all of these—artillery fire steadily eroded the number of effectives in the two brigades. At 6 o'clock in the afternoon the whole Union Brigade could field only three squadrons, though these countercharged French cavalry, losing half their number in the process. At the end of the fighting, the two brigades, by this time combined, could muster one squadron.
Fourteen thousand French troops of d'Erlon's I Corps had been committed to this attack. The I Corps had been driven in rout back across the valley, costing Napoleon 3,000 casualties including over 2,000 prisoners taken. Also some valuable time was lost, as the charge had dispersed numerous units and it would take until 16:00 for d'Erlon's shaken corps to reform. And although elements of the Prussians now began to appear on the field to his right, Napoleon had already ordered Lobau's VI corps to move to the right flank to hold them back before d'Erlon's attack began.

The French cavalry attack: A little before 16:00, Ney noted an apparent exodus from Wellington's centre. He mistook the movement of casualties to the rear for the beginnings of a retreat, and sought to exploit it. Following the defeat of d'Erlon's Corps, Ney had few infantry reserves left, as most of the infantry had been committed either to the futile Hougoumont attack or to the defence of the French right. Ney therefore tried to break Wellington's centre with cavalry alone. Initially, Milhaud's reserve cavalry corps of cuirassiers and Lefebvre-Desnoëttes' light cavalry division of the Imperial Guard, some 4,800 sabres, were committed. When these were repulsed, Kellermann's heavy cavalry corps and Guyot's heavy cavalry of the Guard were added to the massed assault, a total of around 9,000 cavalry in 67 squadrons. When Napoleon saw the charge he said it was an hour too soon.

Wellington's infantry responded by forming squares (hollow box-formations four ranks deep). Squares were much smaller than usually depicted in paintings of the battle—a 500-man battalion square would have been no more than 60 feet (18 m) in length on a side. Infantry squares that stood their ground were deadly to cavalry, as cavalry could not engage with soldiers behind a hedge of bayonets, but were themselves vulnerable to fire from the squares. Horses would not charge a square, nor could they be outflanked, but they were vulnerable to artillery or infantry. Wellington ordered his artillery crews to take shelter within the squares as the cavalry approached, and to return to their guns and resume fire as they retreated.
Witnesses in the British infantry recorded 12 assaults. However, due to the wide frontage of cavalry formations and the 950m space between Hougoumont and La Haie Sainte, any massed cavalry advance would, in reality, consist of a number of successive waves.  Kellermann, recognising the futility of the attacks, tried to reserve the elite carabinier brigade from joining in, but eventually Ney spotted them and insisted on their involvement.
A British eyewitness of the first French cavalry attack, an officer in the Foot Guards, recorded his impressions very lucidly and somewhat poetically:

About four p.m., the enemy's artillery in front of us ceased firing all of a sudden, and we saw large masses of cavalry advance: not a man present who survived could have forgotten in after life the awful grandeur of that charge. You discovered at a distance what appeared to be an overwhelming, long moving line, which, ever advancing, glittered like a stormy wave of the sea when it catches the sunlight. On they came until they got near enough, whilst the very earth seemed to vibrate beneath the thundering tramp of the mounted host. One might suppose that nothing could have resisted the shock of this terrible moving mass. They were the famous cuirassiers, almost all old soldiers, who had distinguished themselves on most of the battlefields of Europe. In an almost incredibly short period they were within twenty yards of us, shouting "Vive l'Empereur!" The word of command, "Prepare to receive cavalry", had been given, every man in the front ranks knelt, and a wall bristling with steel, held together by steady hands, presented itself to the infuriated cuirassiers.

In essence this type of massed cavalry attack relied almost entirely on psychological shock for effect. Close artillery support could disrupt infantry squares and allow cavalry to penetrate; at Waterloo, however, co-operation between the French cavalry and artillery was not impressive. The French artillery did not get close enough to the Anglo-allied infantry in sufficient numbers to be decisive. Artillery fire between charges did produce mounting casualties, but most of this fire was at relatively long range and was often indirect, at targets beyond the ridge.
If infantry being attacked held firm in their square defensive formations, and were not panicked, cavalry on their own could do very little damage to them. The French cavalry attacks were repeatedly repelled by the steadfast infantry squares, the harrying fire of British artillery as the French cavalry recoiled down the slopes to regroup, and the decisive countercharges of Wellington's light cavalry regiments, the Dutch heavy cavalry brigade, and the remaining effectives of the Household Cavalry.

At least one artillery officer disobeyed Wellington's order to seek shelter in the adjacent squares during the charges. Captain Mercer, who commanded 'G' Troop, Royal Horse Artillery, thought the Brunswick troops on either side of him so shaky that he kept his battery of six nine-pounders in action against the cavalry throughout, to great effect.

I thus allowed them to advance unmolested until the head of the column might have been about fifty or sixty yards from us, and then gave the word, "Fire!" The effect was terrible. Nearly the whole leading rank fell at once; and the round shot, penetrating the column carried confusion throughout its extent ... the discharge of every gun was followed by a fall of men and horses like that of grass before the mower's scythe. 
For reasons that remain unclear, no attempt was made to spike other Anglo-allied guns while they were in French possession. In line with Wellington's orders, gunners were able to return to their pieces and fire into the French cavalry as they withdrew after each attack. After numerous costly but fruitless attacks on the Mont-Saint-Jean ridge, the French cavalry was spent.
Their casualties cannot easily be estimated. Senior French cavalry officers, in particular the generals, experienced heavy losses. Four divisional commanders were wounded, nine brigadiers wounded, and one killed—testament to their courage and their habit of leading from the front. Illustratively, Houssaye reports that the Grenadiers à Cheval numbered 796 of all ranks on 15 June, but just 462 on 19 June, while the Empress Dragoons lost 416 of 816 over the same period. Overall, Guyot's Guard heavy cavalry division lost 47% of its strength.

Second French infantry attack: Eventually it became obvious, even to Ney, that cavalry alone were achieving little. Belatedly, he organised a combined-arms attack, using Bachelu's division and Tissot's regiment of Foy's division from Reille's II Corps (about 6,500 infantrymen) plus those French cavalry that remained in a fit state to fight. This assault was directed along much the same route as the previous heavy cavalry attacks (between Hougoumont and La Haye Sainte). It was halted by a charge of the Household Brigade cavalry led by Uxbridge. The British cavalry were unable, however, to break the French infantry, and fell back with losses from musketry fire.
Uxbridge recorded that he tried to lead the Dutch Carabiniers, under Major-General Trip, to renew the attack and that they refused to follow him. Other members of the British cavalry staff also commented on this occurrence. However, there is no support for this incident in Dutch or Belgian sources, and Wellington wrote in his Dispatch to Secretary for War Bathurst on 19 June 1815 that General Trip had "conducted himself much to my satisfaction". Uxbridge then ordered a charge by three squadrons of the 3rd Hussars of the King's German Legion. They broke through the French cavalry, but became hemmed in, were cut off and suffered severe losses. Meanwhile, Bachelu's and Tissot's men and their cavalry supports were being hard hit by fire from artillery and from Adam's infantry brigade, and they eventually fell back.
Although the French cavalry caused few direct casualties to Wellington's centre, artillery fire onto his infantry squares caused many. Wellington's cavalry, except for Sir John Vandeleur's and Sir Hussey Vivian's brigades on the far left, had all been committed to the fight, and had taken significant losses. The situation appeared so desperate that the Cumberland Hussars, the only Hanoverian cavalry regiment present, fled the field spreading alarm all the way to Brussels.