I need a Macro that would find the BLANK CELLS in a column. It would delete
the blank cell plus the cells in the preceding 2 columns and shift them up
without impacting the remaining columns or rows across the spreadsheet.
The spreadsheet repeats every 3rd column with a different item and may
extend 90 columns by 1000 rows.
Example:
If C4 is blank, need to delete cells A4:C4 and SHIFT UP the remaining items
in the 3 columns for each blank cell found in column C. This routine would
need to repeat across the spreadsheet until all items are checked.
"Joel" provided me with a routine (excellent help & patience) that has
gotten me this far along under "Capturing data only when a column 'Value'
changes" dated 10/23/08..
A B C D E F
1 Item A 09/30/2008 11:55:00 ON Item B 09/30/2008 11:55:00
2 Item A 09/30/2008 11:56:00 ON Item B 09/30/2008 11:56:00 OPEN
3 Item A 09/30/2008 11:57:00 OFF Item B 09/30/2008 11:57:00 OPEN
4 Item A 09/30/2008 11:58:00 Item B 09/30/2008 11:58:00 CLOSE
5 Item A 09/30/2008 11:59:00 OFF Item B 09/30/2008 11:59:00 CLOSE
6 Item A 10/01/2008 00:00:00 OFF Item B 10/01/2008 00:00:00
7 Item A 10/01/2008 00:01:00 ON Item B 10/01/2008 00:01:00
8 Item A 10/01/2008 00:02:00 OFF Item B 10/01/2008 00:02:00 OPEN
9 Item A 10/01/2008 00:03:00 ON Item B 10/01/2008 00:03:00 OPEN
10 Item A 10/01/2008 00:04:00 Item B 10/01/2008 00:04:00 OPEN
11 Item A 10/01/2008 00:05:00 OFF Item B 10/01/2008 00:05:00 CLOSE
12 Item A 10/01/2008 00:06:00 ON Item B 10/01/2008 00:06:00 CLOSE

Thanks in advance..
Shag (excel crash dummy)

Try this on a copy of your sheet before you install it into your main
program. it is based on the layout in your posting which shows four columns.
If there is acturally only three then change:

For i = 4 To lstCl Step 4

To:

For i = 3 To lstCl Step 3


Sub delCels()
Dim lstRw As Long, lstCl As Long
lstRw = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lstCl = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 4 To lstCl Step 4
For j = lstRw To 2 Step -1
If Cells(j, i) = "" Or IsEmpty(Cells(j, i)) Then
Range(Cells(j, i - 2), Cells(j, i)).Delete
End If
Next
Next
End Sub







"ShagNasty" wrote:

> I need a Macro that would find the BLANK CELLS in a column. It would delete
> the blank cell plus the cells in the preceding 2 columns and shift them up
> without impacting the remaining columns or rows across the spreadsheet.
> The spreadsheet repeats every 3rd column with a different item and may
> extend 90 columns by 1000 rows.
> Example:
> If C4 is blank, need to delete cells A4:C4 and SHIFT UP the remaining items
> in the 3 columns for each blank cell found in column C. This routine would
> need to repeat across the spreadsheet until all items are checked.
> "Joel" provided me with a routine (excellent help & patience) that has
> gotten me this far along under "Capturing data only when a column 'Value'
> changes" dated 10/23/08..
> A B C D E F
> 1 Item A 09/30/2008 11:55:00 ON Item B 09/30/2008 11:55:00
> 2 Item A 09/30/2008 11:56:00 ON Item B 09/30/2008 11:56:00 OPEN
> 3 Item A 09/30/2008 11:57:00 OFF Item B 09/30/2008 11:57:00 OPEN
> 4 Item A 09/30/2008 11:58:00 Item B 09/30/2008 11:58:00 CLOSE
> 5 Item A 09/30/2008 11:59:00 OFF Item B 09/30/2008 11:59:00 CLOSE
> 6 Item A 10/01/2008 00:00:00 OFF Item B 10/01/2008 00:00:00
> 7 Item A 10/01/2008 00:01:00 ON Item B 10/01/2008 00:01:00
> 8 Item A 10/01/2008 00:02:00 OFF Item B 10/01/2008 00:02:00 OPEN
> 9 Item A 10/01/2008 00:03:00 ON Item B 10/01/2008 00:03:00 OPEN
> 10 Item A 10/01/2008 00:04:00 Item B 10/01/2008 00:04:00 OPEN
> 11 Item A 10/01/2008 00:05:00 OFF Item B 10/01/2008 00:05:00 CLOSE
> 12 Item A 10/01/2008 00:06:00 ON Item B 10/01/2008 00:06:00 CLOSE
>
> Thanks in advance..
> Shag (excel crash dummy)
>

There are three columns -- tag, time, & value that repeat. Guess I got happy
fingers when I posted the info...

Thanks,

"JLGWhiz" wrote:

> Try this on a copy of your sheet before you install it into your main
> program. it is based on the layout in your posting which shows four columns.
> If there is acturally only three then change:
>
> For i = 4 To lstCl Step 4
>
> To:
>
> For i = 3 To lstCl Step 3
>
>
> Sub delCels()
> Dim lstRw As Long, lstCl As Long
> lstRw = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
> lstCl = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
> For i = 4 To lstCl Step 4
> For j = lstRw To 2 Step -1
> If Cells(j, i) = "" Or IsEmpty(Cells(j, i)) Then
> Range(Cells(j, i - 2), Cells(j, i)).Delete
> End If
> Next
> Next
> End Sub
>
>
>
>
>
>
>
> "ShagNasty" wrote:
>
> > I need a Macro that would find the BLANK CELLS in a column. It would delete
> > the blank cell plus the cells in the preceding 2 columns and shift them up
> > without impacting the remaining columns or rows across the spreadsheet.
> > The spreadsheet repeats every 3rd column with a different item and may
> > extend 90 columns by 1000 rows.
> > Example:
> > If C4 is blank, need to delete cells A4:C4 and SHIFT UP the remaining items
> > in the 3 columns for each blank cell found in column C. This routine would
> > need to repeat across the spreadsheet until all items are checked.
> > "Joel" provided me with a routine (excellent help & patience) that has
> > gotten me this far along under "Capturing data only when a column 'Value'
> > changes" dated 10/23/08..
> > A B C D E F
> > 1 Item A 09/30/2008 11:55:00 ON Item B 09/30/2008 11:55:00
> > 2 Item A 09/30/2008 11:56:00 ON Item B 09/30/2008 11:56:00 OPEN
> > 3 Item A 09/30/2008 11:57:00 OFF Item B 09/30/2008 11:57:00 OPEN
> > 4 Item A 09/30/2008 11:58:00 Item B 09/30/2008 11:58:00 CLOSE
> > 5 Item A 09/30/2008 11:59:00 OFF Item B 09/30/2008 11:59:00 CLOSE
> > 6 Item A 10/01/2008 00:00:00 OFF Item B 10/01/2008 00:00:00
> > 7 Item A 10/01/2008 00:01:00 ON Item B 10/01/2008 00:01:00
> > 8 Item A 10/01/2008 00:02:00 OFF Item B 10/01/2008 00:02:00 OPEN
> > 9 Item A 10/01/2008 00:03:00 ON Item B 10/01/2008 00:03:00 OPEN
> > 10 Item A 10/01/2008 00:04:00 Item B 10/01/2008 00:04:00 OPEN
> > 11 Item A 10/01/2008 00:05:00 OFF Item B 10/01/2008 00:05:00 CLOSE
> > 12 Item A 10/01/2008 00:06:00 ON Item B 10/01/2008 00:06:00 CLOSE
> >
> > Thanks in advance..
> > Shag (excel crash dummy)
> >

Thanks...

"The Code Cage Team" wrote:

>
> Hi drop all this in a standard module, the code will look at every 3rd
> column up to a maximum of all used coulmns and work up from the last
> used cell when it finds a blank it will delete the cells for all 3
> columns in that row then it will move 3 columns over and do the same!
>
> Sub delete_blanks()
> Dim Rng As Range, MyCell As Range
> Dim i As Long, r As Long
> Dim C1 As String, C2 As String
> For i = 3 To ActiveSheet.UsedRange.Columns.Count Step 3
> C1 = ColumnLetter(i - 0)
> C2 = ColumnLetter(i - 2)
> For r = Range(C1 & Rows.Count).End(xlUp).Row To 1 Step -1
> If Range(C1 & r).Value = "" Then
> Range(C1 & r & ":" & C2 & r).Delete shift:=xlUp
> End If
> Next r
> Next i
> End Sub
> Function ColumnLetter(ColumnNumber As Integer) As String
> If ColumnNumber > 26 Then
>
> ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
> Chr(((ColumnNumber - 1) Mod 26) + 65)
> Else
> ColumnLetter = Chr(ColumnNumber + 64)
> End If
> End Function
>
>
> --
> The Code Cage Team
>
> Regards,
> The Code Cage Team
> www.thecodecage.com
> ------------------------------------------------------------------------
> The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=7373
>
>