Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete blank columns
Hi,
I have linked two sheets and would like to delete all blanks in the second sheets. I'm using this macro: Sub delete_rows() Dim RowNdx As Long Dim LastRow As Long LastRow = ActiveSheet.UsedRange.Rows.Count For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "B").Value = "" Then ' rows(RowNdx).Delete Rows(RowNdx).EntireRow.Hidden = True End If Next RowNdx End Sub I want to make two adjusments to this macro: 1. This macro hides the blank columns- I want to delete them 2. I would like it to update automatically when I update the sheets 3. I would like the numbering to follow with the filled columns Is this possible? Appreciate all the help I can get! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete blank columns
You have not specified in which sheet you need to delete rows. Replace
ActiveSheet in the code with Sheets("Sheet2") if you want to delete rows in sheet2 1. This macro hides the blank columns- I want to delete them Dim RowNdx As Long Dim LastRow As Long LastRow = ActiveSheet.UsedRange.Rows.Count For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "B").Value = "" Then Rows(RowNdx).Delete End If Next RowNdx End Sub 2. I would like it to update automatically when I update the sheets Launch VBE using Alt+F11. Double click 'This Workbook' and drop down to get this event and paste the above code under this ; which would update sheet2 on Save. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub If you want to change it as soon as you make changes in sheet1 then double click Sheet1 in VBE and dropdown to find the below and paste the code. Private Sub Worksheet_Change(ByVal Target As Range) End Sub 3. I would like the numbering to follow with the filled columns The best way is to put the formula in Column A of Sheet2 and copy that to all rows in Column A. This will autoadjust the numbering = Row() =Row()+1 -- If this post helps click Yes --------------- Jacob Skaria "Johannes_R" wrote: Hi, I have linked two sheets and would like to delete all blanks in the second sheets. I'm using this macro: Sub delete_rows() Dim RowNdx As Long Dim LastRow As Long LastRow = ActiveSheet.UsedRange.Rows.Count For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "B").Value = "" Then ' rows(RowNdx).Delete Rows(RowNdx).EntireRow.Hidden = True End If Next RowNdx End Sub I want to make two adjusments to this macro: 1. This macro hides the blank columns- I want to delete them 2. I would like it to update automatically when I update the sheets 3. I would like the numbering to follow with the filled columns Is this possible? Appreciate all the help I can get! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete blank columns
On Tue, 7 Apr 2009 04:31:01 -0700, Jacob Skaria
wrote: If this post helps click Yes --------------- Jacob Skaria Whoever the total retard is that made this stupid little sig addendum to you guys' posts is about as stupid as it gets. There are NO BUTTONS in Usenet! So why would an IDIOT programmer add a TEXT based sig to his retarded GUI interface, which he wrongfully assumed that everyone uses? Most folks read this group in TEXT mode. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically delete rows with any blank columns | Excel Worksheet Functions | |||
Delete blank columns. | Excel Discussion (Misc queries) | |||
Auto Delete Columns in a Macro that are Blank | Excel Discussion (Misc queries) | |||
Delete rows with certain columns blank | Excel Discussion (Misc queries) | |||
Delete rows that contain blank columns | Excel Worksheet Functions |