How do you create a delete button for a form with a macro in Excel?

I managed to create a form to add records to a worksheet. Now what I want to do is add a 'Delete Record' button to the form to delete the last entry (or any other entry for that matter). First, how do you identify the entry you wish to delete? My solution is to use the first column as a reference column. Try using a macro like this:

Private Sub CommandButton1_Click()

Dim c As Long

Dim iRecord As String

Dim Deleted As Boolean

 

iRecord = InputBox("Enter Record Number to Delete")

c = Cells(Rows.Count, 1).End(xlUp).Row

Deleted = False

While Not Deleted

If Cells(c, 1) = iRecord Then

Rows(c).Delete shift:=xlUp

Deleted = True

End If

c = c - 1

If c <= 0 Then Deleted = True

Wend

End Sub

How do you flip or reverse a column of data in Excel?

How do you flip or reverse a column of data in an Excel spreadsheet? Well, if you simply want to reverse a list that's already in alpha or numeric order that is easily accomplished using the built in "Sort: feature (you can also easily transpose columns into rows and vice versa).

However, often you will find yourself with a column of data that isn't sorted (and may even be in different formats like a mix of numbers and text). How do you simply flip it so the bottommost items are on top and the top is now the bottom of the column of data?

There are a number of ways to do this, including writing a macro, but the easiest way I can think of is to simply create what I like to call a helper column. First, select the column you want to flip, right click it, and choose Insert to add your new helper column. Add a list of numbers starting with 1 and running down to the number of items in your list. Now select both of those numbers and then double-click the lower right corner of the cell that contains the 2 to quickly fill down the length of your target column.

Select your helper column and any other columns you want sorted along with it. Go to the Data tab and click the Sort button and use this to Sort by your helper column using A to Z or largest to smallest. That's it. Once completed, you can delete your helper column. Simple, eh?

Another more advanced method is to use a macro. Try this:

Sub FlipColumns()

Dim vTop As Variant

Dim vEnd As Variant

Dim iStart As Integer

Dim iEnd As Integer

Application.ScreenUpdating = False

iStart = 1

iEnd = Selection.Columns.Count

Do While iStart < iEnd

vTop = Selection.Columns(iStart)

vEnd = Selection.Columns(iEnd)

Selection.Columns(iEnd) = vTop

Selection.Columns(iStart) = vEnd

iStart = iStart + 1

iEnd = iEnd - 1

Loop

Application.ScreenUpdating = True

End Sub

 

Shared Apartment Expenses for 5 Roommates Spreadsheet

I recently updated my shared apartment expenses worksheet to account for up to five roommates. The purpose of this spreadsheet is to easily track who you owe money to. It's an easy way to divide up rent, cable, electricity, groceries and random other oddities. The instructions are included with the spreadsheet. You can download the shared expenses workbook here.


Shared Apartment Expenses for 5 Roommates Spreadsheet.xls